Iudith Mentzel went exploring and came up with some interesting tidbits that I thought I would share.
First, she noted that "this quiz was very similar with the quiz from 21 April 2011. The only difference was that it mentioned a nested block for the procedure that is calling DBMS_UTILITY.FORMAT_CALL_STACK...by the way, in comparison with the previous one that was scored as Advanced, this one was even trickier and scored as Intermediate, which looks not very consistent."
You are absolutely right, Iudith. That was not very consistent. Among other things, once a reviewer pointed out that the bottom of the stack could under some circumstances not be an anonymous block, I should have changed it to Advanced.
With my spare moments, I am going back to older quizzes and cleaning them up (adding lesson summaries and verification code, for example, since these were not even stored with questions when the PL/SQL Challenge first started). In the process I am finding many quizzes whose levels of difficulty and other information should probably be adjusted. These changes would, however, also affect scoring. So at some point, we will tweak our backend so that "old" scores and ranking data are archived and will not be affected by changes in the questions.
Now on to more substantive thoughts from Iudith on the call stack function (her words in blue):
Checking the quiz scenario, I found some strange issues, here is my test case:
CREATE OR REPLACE PROCEDURE plch_proc1
IS
BEGIN
BEGIN
DBMS_OUTPUT.put_line (DBMS_UTILITY.format_call_stack);
END;
END;
/
-- Procedure created.
CREATE OR REPLACE PACKAGE plch_pkg1
IS
PROCEDURE proc2;
END plch_pkg1;
/
-- Package created.
CREATE OR REPLACE PACKAGE BODY plch_pkg1
IS
PROCEDURE proc2
IS
BEGIN
plch_proc1;
END;
END plch_pkg1;
/
-- Package body created.
CREATE OR REPLACE PROCEDURE plch_proc3
IS
BEGIN
plch_pkg1.proc2;
END;
/
-- Procedure created.
-- testing
BEGIN
plch_proc3;
END;
/
----- PL/SQL Call Stack -----
object line object
handle number name
000007FFD219EC48 5 procedure SCOTT.PLCH_PROC1
000007FFD1FF5810 6 package body SCOTT.PLCH_PKG1
000007FFD1F54060 4 procedure SCOTT.PLCH_PROC3
000007
For some reason, SQL*PLUS client (version 9i) did not display the output correctly; the "anonymous block" is NOT there ... though it should be.The above output looks like this after some editing, otherwise the lines were broken in the middle ...This was tested with an 11gR1 database (11.1.0.7.0) having UTF8 character set. The same SQL*PLUS client version, on a single-byte character set database was even stranger:
BEGIN plch_proc3; END; / ERROR: ORA-06502: PL/SQL: numeric or value error: host bind array too small ORA-06512: at line 1
Just "to make peace", I decided to put the value into a table and select afterwards to see it, so I rewrote plch_proc1 ... and now comes the interesting part:
CREATE TABLE plch_temp ( x VARCHAR2(4000) ) / -- Table created. --version 1 - insert the function result directly into the table: CREATE OR REPLACE PROCEDURE plch_proc1 IS BEGIN BEGIN INSERT INTO plch_temp VALUES (DBMS_UTILITY.format_call_stack); END; END; / -- Procedure created. BEGIN plch_proc3; END; / -- PL/SQL procedure successfully completed. -- now we have "anonymous block" TWICE !!! SQL> SELECT * FROM plch_temp 2 / ----- PL/SQL Call Stack ----- object line object handle number name 000007FFD1EE0C38 1 anonymous block 000007FFD219EC48 7 procedure SCOTT.PLCH_PROC1 000007FFD1FF5810 6 package body SCOTT.PLCH_PKG1 000007FFD1F54060 4 procedure SCOTT.PLCH_PROC3 000007FFD1E3EEB8 2 anonymous block 1 row selected. ROLLBACK; / --version 2 - store the function result in a local variable before inserting: CREATE OR REPLACE PROCEDURE plch_proc1 IS x VARCHAR2(4000); BEGIN BEGIN -- DBMS_OUTPUT.put_line (DBMS_UTILITY.format_call_stack); -- INSERT INTO plch_temp VALUES (DBMS_UTILITY.format_call_stack); x := DBMS_UTILITY.format_call_stack; INSERT INTO plch_temp VALUES (x); END; END; / -- Procedure created. BEGIN plch_proc3; END; / -- PL/SQL procedure successfully completed. -- now it is finally as expected SQL> SELECT * FROM plch_temp 2 / ----- PL/SQL Call Stack ----- object line object handle number name 000007FFD219EC48 10 procedure SCOTT.PLCH_PROC1 000007FFD1FF5810 6 package body SCOTT.PLCH_PKG1 000007FFD1F54060 4 procedure SCOTT.PLCH_PROC3 000007FFD1F3F920 2 anonymous block 1 row selected.
Just as a last remark, the command line SQLPLUS.exe of Oracle client version 11g displayed the output almost correctly with the original DBMS_OUTPUT.put_line, that is, "anonymous block" was displayed once, but with the lines still broken in the middle ...
But the above tests that insert into a table look however strange ...it would be interesting to hear if anybody does have any clue for this behavior .
Looks like calling the function DIRECTLY from a SQL statement does generate an additional "anonymous block" layer in the call stack ... which can even become misleading if one seriously considers using this function not just for "PL/SQL fun" ...
I know that you are very "fond of" the DBMS_UTILITY.FORMAT_CALL_STACK function, however, I don't think that using it "as is" is such a "big gift" made to the developers ... Maybe only by hiding it into another "text analyzing" package, like the sample one supplied .
Back to Steven....it is unlikely that very many of you are still running an Oracle9i client, so that is probably not much of an issue, though interesting to discover. Also good to know that calling the call stack function from within an SQL statement, inside PL/SQL, introduces another anonymous block to the stack. It makes sense to me that this "internal" anonymous block would disappear in that last example. The function is not being called from within the SQL statement any longer.
Thanks, once again, to Iudith for her fascinating explorations. Any comments from other players?
Cheers, SF
