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.