Hanged during debugging (PL/SQL Developer)

[Leonardo Gargano]

Hello,

We are getting stored procedure hanged during debugging in PL/SQL Developer. After that I can see PIPE GET wait event . It is happening after I've migrated the database from Oracle Database EE 10g to Oracle Database EE 11g.
I am able to complete the action with SQL Developer (from Oracle).

I

 

[Marco Kalter]

Unfortunately we do not know what causes this issue. The Oracle Debug API seems to be looping in this case, but it is not clear why this happens.

 

[Leonardo Gargano]

But, do you have any suggestion for me?

 

[mike]

The debugger hangs enough that I gave up on it years ago. I most likely am remembering wrong, but it seems like sometimes it worked to compile debug information on all subprograms that the main program calls at the point it hangs, so that might be worth trying.

You said that the debugger works with SQL Developer? That would be the simplest solution.

There's the old way of temporarily putting dbms_output.put_line statements throughout the code to get variable values and stuff.

Where I work, we wrote a custom debug package to write messages to a table, which I like a lot, but might be more work than you were thinking of. We then instrument our code. Every time we enter/exit a procedure, we call the debug package. Every time a variable changes, we call the debug package with the variable value. We have a profile defined so we can switch the debug on and off. Also, most of our programs have a parameter so we can call it to run for one record. Like one employee, for example. I have 3 asterisks by key values, so I can easily find them in the table. The program indents the message based on ENTER or EXIT in the text so it helps find the procedures.

 

[Gustavo]

I used to have this problem in RAC environments. Connecting to a single cluster (must update TNSNAMES for this) has solved this problem in at least 3 of my customers.