Database Development

CREATE VIEW AS SELECT * does not dynamically adjust to the underlying object

by

It never ceases to amaze me how I can be humbled every day.

When you work with a technology for so long, teaching, writing, presenting, training and mentoring…you start to think you’re an expert.
You think by now you know all the basics, advanced aspects and nuances.
And yet, despite your experience, there it is: an aspect of Oracle that has been there all along, but somehow I missed it! 25 years doing this for a living, and I missed it!

That or I’m starting to go senile.

In every Oracle development environment in which I’ve worked, either I or an architect that preceded me has included in the SQL standard the common rule to always use explicit column lists when building SELECT or INSERT statements. The simplified version of the rule’s justification is that things tend to change and when they do, the generic SELECT * and INSERT with no column list will break your app.

With my current employer’s most recent project, for the first time I consciously chose to break that rule.

I thought my reason was sound, e.g. the remote table on another Oracle database was being altered frequently and I needed our local view of that table to adjust dynamically, rather than have to check in yet another script into our database build every time they added another column. Ask most DBAs what a view actually is, and they tend to agree with the Oracle documentation that says views are stored queries. They don’t contain the data. They contain the query definition to get at the data at runtime when the view is queried.

I figured that if my column list was the taboo “SELECT * FROM {remote table over db link}”, it would automatically see and include the new column(s) as it parsed and executed the SELECT * query.

As I understood it, upon feeding Oracle a view creation statement, it first strips out any SQL comments found at the top or bottom of the query, ensures the syntax is correct, and that it has the privileges it needed to function. After passing validations, it would then take the view’s defining query and store it as-is in the data dictionary. Then when a user queries the view, Oracle retrieves the view SQL out of the data dictionary, merges it with any predicates (WHERE clause criteria) in the user’s query, then handles it from there like any other SQL statement (optimizing, parsing, checking the SQL cache, executing, etc.)

The red text above is incorrect. My problem is found in my misunderstanding of how the view’s defining query is stored. If the defining query follows the common rule of using an explicit column list, it will store that SELECT statement as-is, preserving aliases, character case, comments, etc.  But if the defining query uses SELECT *, Oracle probes the data dictionary of the underlying objects to get every current column name. It then re-writes your “dynamic” SELECT statement to be static, replacing the “*” with a list of upper-cased, double-quoted column names as they stood at the time of view creation.

With a table defined like this:

DROP VIEW dyn_view;
DROP TABLE test_tab PURGE;

CREATE TABLE test_tab (
 ID  INTEGER NOT NULL
,NAME VARCHAR2(100)
,DESCRIPTION VARCHAR2(500)
)
/

INSERT INTO test_tab VALUES (1, 'First Row', 'I am the first row');
COMMIT;

And a “dynamic” adjustable view foolishly created like this:

CREATE VIEW dyn_view AS
SELECT * FROM test_tab;

We can look at how Oracle processed our view, whether it preserved the SELECT * or exploded it into an explicit, static column list:

SQL> SELECT text FROM user_views WHERE view_name = 'DYN_VIEW';
TEXT
----------------------------------------------
SELECT "ID","NAME","DESCRIPTION" FROM test_tab

As you can see above. The data dictionary now contains the current list of columns in test_tab, not the dynamic SELECT * as hoped for.

We can query the view just to see it in action before the table change:

SQL> SELECT * FROM dyn_view;
ID NAME      DESCRIPTION
-- --------- ------------------
 1 First Row I am the first row

Now let’s alter the table, as if we were the merciless department constantly changing the remote table we depend on:

ALTER TABLE test_tab
  ADD load_date DATE DEFAULT SYSDATE NOT NULL;

Did the content of the view happen to change because we changed the underlying object?

  
SQL> SELECT text FROM user_views WHERE view_name = 'DYN_VIEW';
TEXT
----------------------------------------------
SELECT "ID","NAME","DESCRIPTION" FROM test_tab

Nope, sadly as seen above, it is the same definition as it was before. So it is not possible to have a stored query that says “give me whatever you’ve got at the present time.” Instead, Oracle is going to force us to always store a static column list at the time of view creation.

Does the new column exist on the base table?

SQL> SELECT * FROM test_tab;
ID NAME      DESCRIPTION        LOAD_DATE
-- --------- ------------------ -----------
 1 First Row I am the first row 2017Aug17 16:09:39

Just to be doubly-certain, has our view adjusted to include it?

SQL> SELECT * FROM dyn_view;
ID NAME      DESCRIPTION
-- --------- ------------------
 1 First Row I am the first row

No, it has not. Well, that’s that.

So it turns out, the view has to be-recreated with an altered explicit column list to pick up the new column, or the original SELECT * creation script must be re-run:

CREATE OR REPLACE VIEW dyn_view AS
SELECT * FROM test_tab;

We can look at how Oracle processed our view, whether it preserved the SELECT * or exploded it into an explicit, static column list:

SQL> SELECT text FROM user_views WHERE view_name = 'DYN_VIEW';
TEXT
----------------------------------------------
SELECT "ID","NAME","DESCRIPTION","LOAD_DATE" FROM test_tab

And there it is. Now the view finally sees the new column.

Note: While researching the Oracle docs for this topic, I finally found that Oracle does spell this out clearly in the Database Administrator’s Guide, in the section on Managing Views, Sequences and Synonyms, under the heading Expansion of Defining Queries at View Creation Time. It says “When a view is created, Oracle Database expands any wildcard (*) in a top-level view query into a column list.” Whether it said this in the Oracle 7 manuals in 1995 when I first learned about views, is probably not worth pursuing.

It wasn’t until our application broke that I discovered my clever plan to have the view adjust dynamically was fundamentally flawed and would never work. It made me feel like it was my first week on the job with Oracle all over again. Because I had always followed the best practice of explicit column lists, I’d never run into this gap in my knowledge. Quite disappointing, but I learned something new, so it was still a good day.

BULK COLLECT and COUNT() do not raise NO_DATA_FOUND

by

I thought I knew bulk operations well.
Today while coding up a quick hunk of PL/SQL, the code did not behave as expected.
Basically I’d had a senior moment and forgotten something that I think my novice DBA and developer readers might benefit from.

When you try to select some rows from a data structure using a filter, for which none of the rows in the structure are a match, in a SQL client tool, you’ll simply be told that you received 0 rows. But if you are doing the same select statement in PL/SQL to get the output INTO some waiting variable(s), Oracle will raise the NO_DATA_FOUND exception.

DROP TABLE my_emp;

CREATE TABLE my_emp AS SELECT * FROM hr.employees;

SET SERVEROUTPUT ON
DECLARE
   l_email   my_emp.email%TYPE;
   l_last_nm my_emp.last_name%TYPE;
BEGIN
   l_last_nm := '&&last_name';
   SELECT email
     INTO l_email
     FROM my_emp
    WHERE last_name = l_last_nm;
   dbms_output.put_line('Email Handle: ' || l_email);
EXCEPTION
   WHEN no_data_found THEN
      dbms_output.put_line('Could not find employee given last name: ' || l_last_nm);
END;
/

Which produces this output if we give it a non-existent employee last name of “Bogus”

SQL>
Table dropped
Table created
Could not find employee given last name: Bogus
PL/SQL procedure successfully completed

This use of SELECT into a PL/SQL block is known as an explicit cursor and you must code for the NO_DATA_FOUND exception if you use it.
This is a good thing.

Unfortunately, once a new developer gets used to this NO_DATA_FOUND exception, they tend to get carried away with it and use it for basic UPDATE and DELETE DML, which is useless. Instead use cursor attributes SQL%FOUND or SQL%ROWCOUNT to determine if the UPDATE or DELETE worked.

I’ve also inherited lots of code in the past that had little functions and SQL that performed “existence checks” to ensure the data is present before attempted to do DML on the keyed data. This approach is entirely unnecessary and produces code that will never be exercised.

SET SERVEROUTPUT ON
DECLARE
   l_count   INTEGER := 0;
   l_email   my_emp.email%TYPE;
   l_last_nm my_emp.last_name%TYPE;
BEGIN
   l_last_nm := '&&last_name';
   SELECT COUNT(*)
     INTO l_count
     FROM my_emp
    WHERE last_name = l_last_nm;

   IF (l_count > 0) THEN
      SELECT email
        INTO l_email
        FROM my_emp
       WHERE last_name = l_last_nm;
      dbms_output.put_line('Email Handle: ' || l_email);
   END IF;

EXCEPTION
   WHEN no_data_found THEN
      dbms_output.put_line('Could not find employee given last name: ' || l_last_nm);
END;

Lines 21 to 23 will never be hit. Why? Because COUNT() does not raise the NO_DATA_FOUND exception. It returns 0 if no rows match the filter.

Well, my senior moment today was forgetting that BULK COLLECT behaves in a manner similar to COUNT().
The following anonymous block retrieves all attributes for employees who match the given last name.

SET SERVEROUTPUT ON
DECLARE
   l_count    INTEGER := 0;
   l_last_nm  my_emp.last_name%TYPE;
   TYPE       tr_emp_tab IS TABLE OF my_emp%ROWTYPE;
   lr_emp_tab tr_emp_tab;
BEGIN
   l_last_nm := '&&last_name';
   BEGIN
      SELECT *
        BULK COLLECT
        INTO lr_emp_tab
        FROM my_emp
       WHERE last_name = l_last_nm;

      IF (lr_emp_tab IS NOT NULL AND lr_emp_tab.count > 0) THEN
         dbms_output.put_line('Found employees:');
         FOR i IN lr_emp_tab.first .. lr_emp_tab.last LOOP
            dbms_output.put_line(lr_emp_tab(i).first_name || ' ' || lr_emp_tab(i).last_name);
         END LOOP;
      END IF;
   EXCEPTION
      WHEN no_data_found THEN
         dbms_output.put_line('Could not find any employees given last name: ' || l_last_nm);
   END;
END;
/

If we run it for “De Haan”, “King” and “Bogus”, we get the following output.

SQL>
Found employees:
Lex De Haan
PL/SQL procedure successfully completed

SQL>
Found employees:
Steven King
Janette King
PL/SQL procedure successfully completed

SQL>
PL/SQL procedure successfully completed

That last result surprised me. If there were no rows to put into the waiting collection, just like the simple explicit cursor INTO above, I was expecting it to raise the NO_DATA_FOUND exception and spit out the “Could not find…” sentence to the screen. What I had forgotten — and am sharing so you don’t have to learn the hard way — is that if the explicit cursor is using BULK COLLECT and no rows are found, it gives you an initialized empty collection, which we verify with the code listing below.

SET SERVEROUTPUT ON
DECLARE
   l_count    INTEGER := 0;
   l_last_nm  my_emp.last_name%TYPE;
   TYPE       tr_emp_tab IS TABLE OF my_emp%ROWTYPE;
   lr_emp_tab tr_emp_tab;
BEGIN
   l_last_nm := '&&last_name';
   SELECT *
    BULK COLLECT
    INTO lr_emp_tab
    FROM my_emp
   WHERE last_name = l_last_nm;

   IF (lr_emp_tab IS NULL) THEN
      dbms_output.put_line('The receiving collection is atomically NULL');
   ELSE
      IF (lr_emp_tab.COUNT = 0) THEN
         dbms_output.put_line('The receiving collection is empty');
      END IF;
   END IF;

   IF (lr_emp_tab IS NOT NULL AND lr_emp_tab.count > 0) THEN
      dbms_output.put_line('Found employees:');
      FOR i IN lr_emp_tab.first .. lr_emp_tab.last LOOP
         dbms_output.put_line(lr_emp_tab(i).first_name || ' ' || lr_emp_tab(i).last_name);
      END LOOP;
   END IF;
END;
/

If we run it and provide the last name of “Bogus”, it confirms that what BULK COLLECT does is initialize the collection and skip putting anything in it.

SQL>
Table dropped
Table created
The receiving collection is empty
PL/SQL procedure successfully completed

Knowing this, I was able to remove the exception section, and as a side effect made the code more simple and correct.

Live well!