Thursday, August 11, 2016

Qualify names of variables inside SQL statements! (an ode to fine-grained dependency)

Received via email recently:
I made a recommendation as part of our best PL/SQL best practices as follows. 
Always qualify all variable names with the Procedure name. Example:          insert_row.p_cust_name  instead of p_cust_name ​I recollect reading about this is in one of your articles and now I am being asked to justify why I have made this recommendation. Can you please help me convey to my team why this is a good practice?
My quick email response was:
I recommend that you qualify references to variables inside SQL statements embedded in your PLSQL code. This additional information given to the compiler can help it minimize invalidation of program units when dependent objects (such as tables) are changed. 
This feature is known as fine-grained dependency management, which was introduced in Oracle Database 11g. Prior to 11.1, the granularity of automatic dependency management in PL/SQL program units was the database object. 

Suppose, for example, that a procedure P referenced table T, such as with a %ROWTYPE or %TYPE anchored declaration or a SQL statement. Then in 10.2 and earlier, any change to the table whatsoever would cause P to be invalidated. Upon recompilation, the new information about T would be used.

This is quite a wonderful feature, making the need for a make utility in PL/SQL completely unnecessary. The down side of this coarse granularity, however, is that you might make any number of changes to a table that really do not have any impact on a program unit and it would still be invalidated.

Say that procedure P's only dependency on table T was this declaration:

my_dob T.birth_date%TYPE;

Well, then certainly if I add a new column to the procedure, it couldn't possibly affect P. Yet it would still be marked as invalid.

Starting with 11.1, however, the granularity of dependency shifted down a level. Now, table dependencies are tracked down to the column level. Program unit dependencies are shifted down to the parameter list.

The result should be way fewer invalidations of program units as tables are modified. Still, though, if you do not fully qualify all your PL/SQL constants and variables that appear inside SQL statements, you could still force unnecessary invalidations. The following example should make that clear to you.

I create a table and two procedures:

CREATE TABLE t (n NUMBER, d DATE)
/

CREATE OR REPLACE PROCEDURE use_t1
   AUTHID DEFINER
IS
   nn   NUMBER := 1;
   d    DATE;
BEGIN
   SELECT d INTO d
     FROM t
    WHERE n = nn;
END;
/

CREATE OR REPLACE PROCEDURE use_t2
   AUTHID DEFINER
IS
   nn   NUMBER := 1;
   d    DATE;
BEGIN
   SELECT d INTO d
     FROM t
    WHERE n = use_t2.nn;
END;
/

The only difference between use_t1 and use_t2 is that in use_t2 I have qualified the reference to nn in the WHERE clause:

    WHERE n = use_t2.nn;

They both compile without error:

  SELECT object_name || ' - ' || status and_now
    FROM user_objects
   WHERE object_name LIKE 'USE_T%'
ORDER BY object_name
/

AND_NOW
----------------
USE_T1 - VALID
USE_T2 - VALID

Suppose that I now add a new column named "nn" to my table:

ALTER TABLE t ADD nn NUMBER
/

When I run the above query a second time, I now see;

AND_NOW
----------------
USE_T1 - INVALID
USE_T2 - VALID

"But wait!" you are saying to yourself. "Steven just said that if you add a new column, it couldn't possibly affect an existing program, so they wouldn't be invalidated."

Well, I never said adding a column would never affect any program's status. And here you can clearly see the benefit of qualifying references to variables inside SQL:

In use_t1, with my unqualified reference to "nn",  the compiler cannot be certain that changes to tables will not affect the status of the program unit, so it is marked invalid. This is true even if the variable referenced does not share the name withe the new column. In other words, the following program unit is also marked invalid after the column is added:

CREATE OR REPLACE PROCEDURE use_t3
   AUTHID DEFINER
IS
   nnn   NUMBER := 1;
   d    DATE;
BEGIN
   SELECT d INTO d
     FROM t
    WHERE n = nnn;
END;
/

But if every single constant or variable that is placed inside a SQL statement in your PL/SQL code is fully qualified with the name of the program unit, then there is no ambiguity possible (a table can't have the same name as a program unit) and so adding new columns will not force an invalidation of program units that are dependent in some way on that table.

Check out this LiveSQL script on another example of fine-grained dependency management.

And here's the full script of the code shown above if you want to quickly give a try yourself.

CREATE TABLE t
(
   n   NUMBER,
   d   DATE
)
/

CREATE OR REPLACE PROCEDURE use_t1
   AUTHID DEFINER
IS
   nn   NUMBER := 1;
   d    DATE;
BEGIN
   SELECT d
     INTO d
     FROM t
    WHERE n = nn;
END;
/

CREATE OR REPLACE PROCEDURE use_t2
   AUTHID DEFINER
IS
   nn   NUMBER := 1;
   d    DATE;
BEGIN
   SELECT d
     INTO d
     FROM t
    WHERE n = use_t2.nn;
END;
/

CREATE OR REPLACE PROCEDURE use_t3
   AUTHID DEFINER
IS
   nnn   NUMBER := 1;
   d    DATE;
BEGIN
   SELECT d
     INTO d
     FROM t
    WHERE n = nnn;
END;
/

  SELECT object_name || ' - ' || status and_now
    FROM user_objects
   WHERE object_name LIKE 'USE_T%'
ORDER BY object_name
/

ALTER TABLE t
   ADD nn NUMBER
/

  SELECT object_name || ' - ' || status and_now
    FROM user_objects
   WHERE object_name LIKE 'USE_T%'
ORDER BY object_name
/

DROP TABLE t
/

DROP PROCEDURE use_t1
/

DROP PROCEDURE use_t2
/

DROP PROCEDURE use_t3
/

8 comments:

  1. I recommend thinking about this rather differently -- but the upshot on how to write your static SQL is the same. I always stress the importance of aiming, above all else, for correctness. Only then can other things (like avoiding invalidation when you can) enter the discussion. Here, the silent name capture, that Steven shows, demonstrates the risk of a silent change in a program's semantics. You simply must avoid this. And when you do, you get the nice side-benefit of letting fine-grained dependency tracking deliver its full benefit.

    ReplyDelete
  2. An invalidated procedure has never been a problem to me, we have downtimes where we can install our new software and a simple recompile will solve it.
    But you can't emphasise enough that your program will break when you add a column that is named like a variable (in your case nn) if you don't fully qualify your parameters.
    I have to admit that I started qualifying my parameters only recently - shame on me :-) It only worked until now without bugs because we have (enforced) naming conventions that saved us from desaster.

    ReplyDelete
  3. Starting from Oracle 11.1 if you add new column to any table then it will not invalidate any procedure,functions,package & trigger ???

    ReplyDelete
    Replies
    1. No. Sometimes it will cause invalidations - such as when you do not fully qualify all references to PL/SQL variables inside the SQL statement.

      Delete
    2. Steven this "sometimes" is scary..... means better to compile after any change !!!

      Delete
    3. I suggest that is the wrong way to think about it. In a dev environment, the database will largely take care of this for you, automatically recompiling as needed. Yes, this will happen in production, as well, but usually you don't want to rely on that. In dev, however, you make your changes and the next time someone tries to run the dependent code, it is recompiled as needed. Nothing scary there!

      Delete
  4. We might need one more level of dependency check if column is altered in a way that it increases its size from varchar2(10) to varchar2(12) or number(1) to number(19) then it should not invalidate any procedure,functions,package & trigger..... Hoping to see in oracle version 13 or version 14

    ReplyDelete
    Replies
    1. We shall see...though at some point the benefit of the minimized invalidations is overwhelmed by the complexity of handling such nuances. In addition, we want to be rather conservative and not leave things marked as valid when they really should be recompiled.

      Delete