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 who 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 needs 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 and 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 rewrites 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 and populated 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_tabAs 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 rowNow 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_tabNo, it has not. Well, that’s that.
So it turns out, the view has to be re-created 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_tabAnd 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.
Until we meet again to ascend the database mountain!