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
/