this is cut and past from the bug:
The customer is unable to debug parts of his application using SQL Navigator
as it hangs. When some sample plsql code from the application was run
through dbms_debug the same thing happened. Narrowing the plsql code down
the problem occurs when a plsql function is called from SQL. For example:
function F … is
package body PK is
function PF … is
for rec in (select F() …) loop
procedure PP is
x := 10; --> (1)
y := PF; --> (2)
x := 20; --> (3)
y := PF; --> (4)
x := 30; --> (5)
If you break at the line marked:
- (1) the resulting info record has the expected information - program
name, line, stack size etc. Stepping to the next line also shows the
expected information but continuing to the end of the program after this
has no effect on the executing procedure and it does not complete. This
is true whether or not the lines marked (4) and (5) are present.
- (2) the resulting info record has the expected information but stepping to
the next line results in an empty info record. Continuing to the end of
the program has no effect on the executing procedure. However if the
lines marked (4) and (5) are not present then it does continue successfully
and execution of the procedure completes.
- (3) the resulting info record is empty. Continuing to the next line sets
the info record to have details of the line marked (3) instead of the
following line as expected. Continuing to the end of the program has no
effect on the executing procedure.
stepping to the next line. Again continuing to the end of execution has
In the provided testcase it initially breaks at the line marked (2) then
continues to the next line and finally to the end of the program. As this
does not result in execution completing the only way to terminate the
procedure is to use control-C.
None other than to remove the offending code.
Reproduced: RDBMS_MAIN_LINUX_071102, 10.2.0.3, 10.1.0.5, 22.214.171.124
a) Create the user and objects:
sqlplus /nolog @setup
b) Start 2 sessions:
c) Session 1 - compile the package as debug and turn on debugging:
d) Session 2 - attach to the debug session (enter the id from Session 1
at the prompt) and set a break point:
e) Session 1 - run the problem procedure:
f) Session 2 - run to the breakpoint and print out info, continue to the
next break point and print out further info, continue to end of program
g) Session 1 - the execution in Session 1 should have completed as the above
was run but does not. Therefore ctl-C out then turn off debugging:
h) Session 2 - debug the debug off and detach from the session:
This should result in s1_5 completing but depending on which line you
initially broke on it may or may not finish. If not then again use
*** 11/27/07 04:53 am *** (CHG: Sta->16 Asg->NEW OWNER)
*** 11/27/07 04:58 am *** (CHG: Confirmed Flag->Y)
*** 11/27/07 04:58 am *** (CHG: Sta->11 Asg->NEW OWNER)
*** 11/27/07 04:58 am ***
*** 03/12/08 10:13 am *** (CHG: Asg->NEW OWNER)
*** 08/11/08 05:41 pm ***
Just examined the test case using label RDBMS_MAIN_LINUX_080808. This is not
a bug but an incorrect usage of DBMS_DEBUG API.
Inside step (f), the dbms_debug.continue() actually returns with the reason
code dbms_debug.reason_interpreter_starting, which is the event delivered
when the PL/SQL interpreter is called recursively by SQL for the PL/SQL
function myfunc(). At that point, we haven’t stepped to the next line in the
package ski_contract. And that is why no program location is returned and the
client session appears hanging. dbms_debug.continue() should be called again
to allow the PL/SQL execution to continue to step to line 19 in the package.
It is the same case for step (h).
*** 08/11/08 05:45 pm ***
If this test case illustrates how SQL Navigator uses DBMS_DEBUG in this
hanging case, it is an issue with SQL Navigator. It should prepare to receive
the debug event dbms_debug.reason_interpreter_starting while stepping through
a PL/SQL program and continue until the dbms_debug.reason_line or any other
step events is delivered.
*** 08/11/08 05:49 pm *** (CHG: Sta->32)
*** 08/21/08 06:59 am *** (CHG: Sta->92)
*** 08/21/08 06:59 am ***
Though the return code from the continue does not indicate it has not gone to
the next break point, if I make the same call a second time in s2_4.sql then
the testcase does correctly print the line information. Similarly if I
double up the continue to end of program the procedure completes execution.
I hope this help