How Reliable are PL/Scope Results? (9403)

The 9 November quiz on PL/Scope asked players to draw conclusions about a package body from the data in the user_identifiers view.

One of our most dedicated players, Iudith Mentzel, spent some time testing out the results one gets from PL/Scope (and queries against user_identifiers) for various uses of labels and GOTOs. I publish her comments below for your consideration.

From Iudith Mentzel

I don't want to object to either the results, which are somewhat "colorful" , or the PL/Scope feature itself, but it looks like the "safe usage" of the feature is at least a little bit "less wider" that one may be (too optimistically) tempted to believe .

While the reasoning behind the answer presented for each choice is completely logical and probably follows the reasoning that the players used, driving categorical conclusions about the code contents by ONLY looking at the data gathered by the PL/SQL Scope feature can sometimes be a little bit dangerous ...

For example:

The LABEL and GOTO issue seems very clear on a first glance, however, here is a small example of what can happen if we "tweak" the code a little bit:

-- amendment to add a GOTO ...
CREATE OR REPLACE PACKAGE BODY plch_pkg
IS
   PROCEDURE do_stuff
   IS
      l_items   DBMS_SQL.number_table;
   BEGIN
      FORALL indx IN 1 .. l_items.COUNT
         UPDATE plch_stuff
            SET amount = l_items (indx);

      GOTO all_done;
      NULL;
      <<all_done>>

      DBMS_OUTPUT.put_line (SYSDATE);      

   END do_stuff;
END plch_pkg;
/

-- there is NO LABEL at all in the result set, though we have a label and a GOTO !!!
SELECT type, usage, name
  FROM user_identifiers 
 WHERE object_name = 'PLCH_PKG'
 ORDER BY 1, 2
/

TYPE               USAGE       NAME
------------------ ----------- ------------------------------
ITERATOR           DECLARATION INDX
ITERATOR           REFERENCE   INDX
PACKAGE            DECLARATION PLCH_PKG
PACKAGE            DEFINITION  PLCH_PKG
PROCEDURE          DECLARATION DO_STUFF
PROCEDURE          DEFINITION  DO_STUFF
SYNONYM            CALL        PLITBLM
SYNONYM            REFERENCE   DBMS_SQL
VARIABLE           DECLARATION L_ITEMS
VARIABLE           REFERENCE   L_ITEMS
VARIABLE           REFERENCE   L_ITEMS

11 rows selected.

This is probably because the PL/SQL Optimizing compiler has removed the "non-effective" stuff ...but if we replace the NULL with some other stuff:

CREATE OR REPLACE PACKAGE BODY plch_pkg
IS
   PROCEDURE do_stuff
   IS
      l_items   DBMS_SQL.number_table;
   BEGIN
      FORALL indx IN 1 .. l_items.COUNT
         UPDATE plch_stuff
            SET amount = l_items (indx);

      GOTO all_done;
      DBMS_OUTPUT.put_line('Some stuff');
      <<all_done>>

      DBMS_OUTPUT.put_line (SYSDATE);      

   END do_stuff;
END plch_pkg;
/

then the LABEL is back, though the code still performs exactly the same as before!

SELECT type, usage, name
  FROM user_identifiers 
 WHERE object_name = 'PLCH_PKG'
 ORDER BY 1, 2
/

TYPE            USAGE       NAME
--------------- ----------- --------------------
FUNCTION        CALL        SYSDATE
ITERATOR        DECLARATION INDX
ITERATOR        REFERENCE   INDX
LABEL           DECLARATION ALL_DONE
LABEL           REFERENCE   ALL_DONE
PACKAGE         DECLARATION PLCH_PKG
PACKAGE         DEFINITION  PLCH_PKG
PROCEDURE       DECLARATION DO_STUFF
PROCEDURE       DEFINITION  DO_STUFF
SYNONYM         CALL        PLITBLM
SYNONYM         REFERENCE   DBMS_OUTPUT
SYNONYM         REFERENCE   DBMS_SQL
SYNONYM         REFERENCE   DBMS_OUTPUT
VARIABLE        DECLARATION L_ITEMS
VARIABLE        REFERENCE   L_ITEMS
VARIABLE        REFERENCE   L_ITEMS

16 rows selected.

Now I add one more label....

CREATE OR REPLACE PACKAGE BODY plch_pkg
IS
   PROCEDURE do_stuff
   IS
      l_items   DBMS_SQL.number_table;
   BEGIN
      FORALL indx IN 1 .. l_items.COUNT
         UPDATE plch_stuff
            SET amount = l_items (indx);

      GOTO all_done;
      NULL;
      <<all_done>>

      DBMS_OUTPUT.put_line (SYSDATE);      

      <<another_label>>
      DBMS_OUTPUT.put_line (SYSDATE);
   END do_stuff;
END plch_pkg;
/

And still no LABEL seen in the output, though we have two labels and one GOTO ...

SELECT type, usage, name
  FROM user_identifiers 
 WHERE object_name = 'PLCH_PKG'
 ORDER BY 1, 2
/

TYPE            USAGE       NAME
--------------- ----------- --------------------
ITERATOR        DECLARATION INDX
ITERATOR        REFERENCE   INDX
PACKAGE         DECLARATION PLCH_PKG
PACKAGE         DEFINITION  PLCH_PKG
PROCEDURE       DECLARATION DO_STUFF
PROCEDURE       DEFINITION  DO_STUFF
SYNONYM         CALL        PLITBLM
SYNONYM         REFERENCE   DBMS_SQL
VARIABLE        DECLARATION L_ITEMS
VARIABLE        REFERENCE   L_ITEMS
VARIABLE        REFERENCE   L_ITEMS

11 rows selected.

However, it all depends on where the label is located, for example:

-- adding still another label, but at the beginning 
CREATE OR REPLACE PACKAGE BODY plch_pkg
IS
   PROCEDURE do_stuff
   IS
      l_items   DBMS_SQL.number_table;
   BEGIN
      <<still_another_label>>
      FORALL indx IN 1 .. l_items.COUNT
         UPDATE plch_stuff
            SET amount = l_items (indx);

      GOTO all_done;
      NULL;
      <<all_done>>

      DBMS_OUTPUT.put_line (SYSDATE);      

      <<another_label>>
      DBMS_OUTPUT.put_line (SYSDATE);
   END do_stuff;
END plch_pkg;
/

-- now the first label only appears ...
SELECT type, usage, name
  FROM user_identifiers 
 WHERE object_name = 'PLCH_PKG'
 ORDER BY 1, 2
/

TYPE               USAGE       NAME
------------------ ----------- ------------------------------
ITERATOR           DECLARATION INDX
ITERATOR           REFERENCE   INDX
LABEL              DECLARATION STILL_ANOTHER_LABEL
PACKAGE            DECLARATION PLCH_PKG
PACKAGE            DEFINITION  PLCH_PKG
PROCEDURE          DECLARATION DO_STUFF
PROCEDURE          DEFINITION  DO_STUFF
SYNONYM            CALL        PLITBLM
SYNONYM            REFERENCE   DBMS_SQL
VARIABLE           DECLARATION L_ITEMS
VARIABLE           REFERENCE   L_ITEMS
VARIABLE           REFERENCE   L_ITEMS

12 rows selected.

Now we have two labels, but still only one LABEL declaration, though they are both "equally uneffective" ...

Below I have two labels, one the target of a GOTO, but still no label appears in the user_identifiers view:

CREATE OR REPLACE PACKAGE BODY plch_pkg
IS
   PROCEDURE do_stuff
   IS
      l_items   DBMS_SQL.number_table;
   BEGIN
      FORALL indx IN 1 .. l_items.COUNT
         UPDATE plch_stuff
            SET amount = l_items (indx);

      GOTO another_label;
      NULL;
      <<all_done>>

      DBMS_OUTPUT.put_line (SYSDATE);      

      <<another_label>>
      DBMS_OUTPUT.put_line (SYSDATE);
   END do_stuff;
END plch_pkg;
/

-- still no label, though here logic does matter !
SELECT type, usage, name
  FROM user_identifiers 
 WHERE object_name = 'PLCH_PKG'
 ORDER BY 1, 2
/

TYPE            USAGE       NAME
--------------- ----------- --------------------
ITERATOR        DECLARATION INDX
ITERATOR        REFERENCE   INDX
PACKAGE         DECLARATION PLCH_PKG
PACKAGE         DEFINITION  PLCH_PKG
PROCEDURE       DECLARATION DO_STUFF
PROCEDURE       DEFINITION  DO_STUFF
SYNONYM         CALL        PLITBLM
SYNONYM         REFERENCE   DBMS_SQL
VARIABLE        DECLARATION L_ITEMS
VARIABLE        REFERENCE   L_ITEMS
VARIABLE        REFERENCE   L_ITEMS

11 rows selected.

The label was effective, but still NOT shown in user_identifiers. The CALL to SYSDATE also NOT shown !!! The reference to DBMS_OUTPUT synonym also NOT shown !!!

I just wanted to emphasize how volatile it is to drive conclusions about source code based ONLY on the results in USER_IDENTIFIERS ...

These results seem to be generated AFTER the compiler optimizes the source code so, at least in some aspects, they may be misleading ...

Regarding the choice that asked about FORALL, though the reasoning behind it seems correct, equally to you and to us, in an after-thought it also can be argued ...and this because using FORALL requires a collection to be used, and that would probably introduce additional data into the USER_IDENTIFIERS result set, whether it is a DBMS_SQL based collection, one based on a locally defined TYPE or even on a type referenced from some other package ...

The output for the sample package shown in the Verification code looks like this:

CREATE OR REPLACE PACKAGE BODY plch_pkg
IS
   PROCEDURE do_stuff
   IS
      l_items   DBMS_SQL.number_table;
   BEGIN
      FORALL indx IN 1 .. l_items.COUNT
         UPDATE plch_stuff
            SET amount = l_items (indx);

      <<all_done>>
      DBMS_OUTPUT.put_line (SYSDATE);
   END do_stuff;
END plch_pkg;
/

-- we see some SYNONYMS in the output, that were not there in the original quiz
SELECT type, usage, name
  FROM user_identifiers 
 WHERE object_name = 'PLCH_PKG'
 ORDER BY 1, 2
/

TYPE               USAGE       NAME
------------------ ----------- ------------------------------
FUNCTION           CALL        SYSDATE
ITERATOR           DECLARATION INDX
ITERATOR           REFERENCE   INDX
LABEL              DECLARATION ALL_DONE
PACKAGE            DECLARATION PLCH_PKG
PACKAGE            DEFINITION  PLCH_PKG
PROCEDURE          DECLARATION DO_STUFF
PROCEDURE          DEFINITION  DO_STUFF
SYNONYM            CALL        PLITBLM
SYNONYM            REFERENCE   DBMS_OUTPUT
SYNONYM            REFERENCE   DBMS_SQL
VARIABLE           DECLARATION L_ITEMS
VARIABLE           REFERENCE   L_ITEMS
VARIABLE           REFERENCE   L_ITEMS

14 rows selected.

A last remark is about deciding whether a variable is defined at the package level or inside a subprogram: I think this can be done (maybe preferably) by checking whether the "parent" (the context owner) of the variable declaration is the PACKAGE, rather than a subprogram, for example:

--check variable context ownership
CREATE OR REPLACE PACKAGE BODY plch_pkg
IS
   g_variable   NUMBER ;

   PROCEDURE do_stuff
   IS
      l_items   DBMS_SQL.number_table;
   BEGIN
      FORALL indx IN 1 .. l_items.COUNT
         UPDATE plch_stuff
            SET amount = l_items (indx);

      <<all_done>>
      DBMS_OUTPUT.put_line (SYSDATE);
   END do_stuff;
END plch_pkg;
/

COLUMN TYPE FORMAT A15
COLUMN NAME FORMAT A20

SELECT var.type, var.usage, var.name, parent.type, parent.usage, parent.name
  FROM user_identifiers  var,
       user_identifiers  parent
 WHERE var.object_name = 'PLCH_PKG'
   AND var.type        = 'VARIABLE'
   AND var.usage       = 'DECLARATION'
   AND parent.object_name = var.object_name
   AND parent.object_type = var.object_type
   AND parent.usage_id    = var.usage_context_id
 ORDER BY 1, 2
/

TYPE            USAGE       NAME                 TYPE            USAGE       NAME
--------------- ----------- -------------------- --------------- ----------- ----------------
VARIABLE        DECLARATION L_ITEMS              PROCEDURE       DEFINITION  DO_STUFF
VARIABLE        DECLARATION G_VARIABLE           PACKAGE         DEFINITION  PLCH_PKG

2 rows selected.

In summary, looking only at the data in USER_IDENTIFIERS, we cannot derive 100% precise (YES/NO) conclusions regarding ALL aspects the code ... Just a few thoughts regarding a tough quiz ... and, as I see, not just for me ...For some reason, it reminds me of the one related to "implicit conversions" in the previous quarter ...

Best Regards,
Iudith