view – Database Sherpa

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.

On top of using this blog to speak on data and database design and development, I’ll also be using it as a notebook, documenting the moments of discovery and clarity that occur when dealing with problems. I wish I’d been taking notes this way since I began my career in 1995, instead of just hoping I’d remember the next time I ran into the same problem.

Today I had to learn the hard way (or re-learn; did I learn this before?) that when you replace a view that happens to have an instead-of trigger attached to it, the CREATE OR REPLACE will drop the instead-of trigger silently. CREATE OR REPLACE lulled me into a false sense of security. It seems to imply that the object and its dependencies will not be dropped, but left alone as the source code for the view is replaced in the data dictionary.

Not so.

This “discovery” was found due to things that broke in production. My tested script replaced the view, but silently and viciously removed the important instead-of trigger that belonged to the view.

Lets have a look to be sure I’m not hallucinating:

DROP VIEW my_emp_vw;
DROP TABLE my_emp;

CREATE TABLE my_emp AS SELECT * FROM hr.employees;

CREATE OR REPLACE VIEW my_emp_vw
AS
SELECT * FROM my_emp WHERE my_emp.department_id = 90;

CREATE OR REPLACE TRIGGER my_emp_vw_iot
  INSTEAD OF INSERT OR UPDATE OR DELETE
  ON my_emp_vw 
  FOR EACH ROW
DECLARE
BEGIN
  IF (inserting) THEN
     INSERT INTO my_emp
        (employee_id
        ,first_name
        ,last_name
        ,email
        ,phone_number
        ,hire_date
        ,job_id
        ,salary
        ,commission_pct
        ,manager_id
        ,department_id)
     VALUES
        (:new.employee_id
        ,:new.first_name
        ,:new.last_name
        ,:new.email
        ,:new.phone_number
        ,:new.hire_date
        ,:new.job_id
        ,:new.salary
        ,:new.commission_pct
        ,:new.manager_id
        ,:new.department_id);
     
  ELSIF (updating) THEN
     UPDATE my_emp
        SET first_name = :new.first_name,
            last_name = :new.last_name,
            email = :new.email,
            phone_number = :new.phone_number,
            hire_date = :new.hire_date,
            job_id = :new.job_id,
            salary = :new.salary,
            commission_pct = :new.commission_pct,
            manager_id = :new.manager_id,
            department_id = :new.department_id
      WHERE employee_id = :new.employee_id;
  
  ELSIF (deleting) THEN
     DELETE FROM my_emp
       WHERE employee_id = :old.employee_id;
  END IF;
END my_emp_vw_iot;
/

Now that the objects have been created, let’s look at the state and existence of our objects in the data dictionary.

SELECT object_name, object_type FROM user_objects WHERE object_name LIKE 'MY%';

OBJECT_NAME     OBJECT_TYPE
--------------- -----------------------
MY_EMP_VW_IOT   TRIGGER
MY_EMP_VW       VIEW
MY_EMP          TABLE

Now lets edit the view in place (in actuality it is dropping the view and re-creating it):

CREATE OR REPLACE VIEW my_emp_vw
AS
SELECT * FROM my_emp WHERE my_emp.department_id = 50;

And check whether the trigger was maintained after the CREATE OR REPLACE

SELECT object_name, object_type FROM user_objects WHERE object_name LIKE 'MY%';

OBJECT_NAME     OBJECT_TYPE
--------------- -----------------------
MY_EMP_VW       VIEW
MY_EMP          TABLE

Sure enough. The dependent trigger MY_EMP_VW_IOT was not maintained. It was silently dropped during the REPLACE part of the CREATE OR REPLACE VIEW statement. So be careful. Do better than I. Remember this every time you script a view replacement. The instead-of trigger will have to be re-created.

Live well.

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

Dropping a View Drops the Instead-of Triggers