Skip to main content

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
/

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

Post a Comment

Popular posts from this blog

Quick Guide to User-Defined Types in Oracle PL/SQL

A Twitter follower recently asked for more information on user-defined types in the PL/SQL language, and I figured the best way to answer is to offer up this blog post. PL/SQL is a strongly-typed language . Before you can work with a variable or constant, it must be declared with a type (yes, PL/SQL also supports lots of implicit conversions from one type to another, but still, everything must be declared with a type). PL/SQL offers a wide array of pre-defined data types , both in the language natively (such as VARCHAR2, PLS_INTEGER, BOOLEAN, etc.) and in a variety of supplied packages (e.g., the NUMBER_TABLE collection type in the DBMS_SQL package). Data types in PL/SQL can be scalars, such as strings and numbers, or composite (consisting of one or more scalars), such as record types, collection types and object types. You can't really declare your own "user-defined" scalars, though you can define subtypes  from those scalars, which can be very helpful from the p

The differences between deterministic and result cache features

 EVERY once in a while, a developer gets in touch with a question like this: I am confused about the exact difference between deterministic and result_cache. Do they have different application use cases? I have used deterministic feature in many functions which retrieve data from some lookup tables. Is it essential to replace these 'deterministic' key words with 'result_cache'?  So I thought I'd write a post about the differences between these two features. But first, let's make sure we all understand what it means for a function to be  deterministic. From Wikipedia : In computer science, a deterministic algorithm is an algorithm which, given a particular input, will always produce the same output, with the underlying machine always passing through the same sequence of states.  Another way of putting this is that a deterministic subprogram (procedure or function) has no side-effects. If you pass a certain set of arguments for the parameters, you will always get

My two favorite APEX 5 features: Regional Display Selector and Cards

We (the over-sized development team for the PL/SQL Challenge - myself and my son, Eli) have been busy creating a new website on top of the PLCH platform (tables and packages): The Oracle Dev Gym! In a few short months (and just a part time involvement by yours truly), we have leveraged Oracle Application Express 5 to create what I think is an elegant, easy-to-use site that our users will absolutely love.  We plan to initially make the Dev Gym available only for current users of PL/SQL Challenge, so we can get feedback from our loyal user base. We will make the necessary adjustments and then offer it for general availability later this year. Anyway, more on that as the date approaches (the date being June 27, the APEX Open Mic Night at Kscope16 , where I will present it to a packed room of APEX experts). What I want to talk about today are two features of APEX that are making me so happy these days: Regional Display Selector and Cards. Regional Display Sel