10 November 2011

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

09 November 2011

Can Choice Be Correct If Error Raised? (9401)

The 7 November quiz tested your knowledge of the fact that this statement:
DROP PACKAGE
will drop both the package specification and body.

Several players complained that we marked this choice as correct :
DROP PACKAGE plch_pkg
/

DROP PACKAGE BODY plch_pkg
/
As one person wrote: "If you drop the a package and then try to drop the package body, you get the error ORA-04043: object PLCH_PKG does not exist. Answer 8571 is NOT correct."

One person even went so as to say: "I really don't like to have the solution DROP PACKAGE plch_pkg / DROP PACKAGE BODY plch_pkg / scored as correct. Yes the correct outcome is shown, BUT it is definitely bad style. And knowing that an error will pop up made me not choosing this answer, so I'm blamed again for good style. I know that you will not rescore the answer. But this kind of scoring makes me think to withdraw from the PL/SQL Challenge. So please harden your choices to support good style as well."

I must admit to being a little taken aback by these responses. Let's first address the "correctness" issue and then move on to what we should and should not include as correct choices in future quizzes.

This choice was marked correct because we asked this:

Which of the choices will result in the following two lines being displayed? [after calling a stored procedure that shows the status of the specified database object]
PACKAGE PLCH_PKG: UNDEFINED
PACKAGE BODY PLCH_PKG: UNDEFINED
It is, without doubt, true that if you run the two DROP statements above (a) those two lines of text will be displayed (so I don't see why a rescoring should be done), as both spec and body are gone, and (b) the second statement will result in Oracle throwing this error:
ORA-04043: object string does not exist
    Cause: An object name was specified that was not recognized by the system.
Now, as noted above, the text will be displayed as required, so I do not think that any changes to the scoring should be performed.

But should I not include such choices in the quiz? And if I do, should I always mark them as incorrect because they are "bad code"?

Certainly, it needs to be very clear that the second statement is unnecessary and will throw the error. More generally, we should provide strong recommendations against using a certain approach if it is problematic in some way.

But never include choices like this marked as "correct"? I just don't see why we would exclude such things. There is usually some sort of lesson to be learned, some way to help you reflect on the way you do your work, and the kinds of traps you can fall into.

In this case,  for example, I could see a developer who wasn't clear on the concept that DROP PACKAGE drops the body put such statements in their clean-up scripts. They might never have noticed that errors were being raised, because the end result met their requirements: packages all gone.

So they would in this case have marked that choice as correct, but then learned from reading the explanation that it is not necessary and should therefore be removed. Lesson learned.

What are your thoughts?

Cheers,
Grandpa Steven