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
