Request: SQL window in debugger

[Claus Pedersen]

I would like to have a possibility to view/select database values during debug sessions. During debug sessions it may be difficult to see results of issued updates etc. It would be nice to have the possibility to open a SQL window inside the debug session to view database values.

Note: I have requested this before in a mail to Support (December 15, 2005) but I would like to know if there are any news about the subject.

 

[ScottMattes]

while it is not exactly what you want (I, too, would like adhoc query ability in the midst of debugging the deep dark recesses of a package) have you tried defining a cursor before and after your call to the routine to be tested? It lets you see a snapshot of your data.

CURSOR :asdf IS SELECT * FROM table;

open :asdf;

 

[Claus Pedersen]

Hello Scott.

Thank's for your reply.

I am a little confused about the usage of your suggested cursor, though.

I make a test script like the above and define a variable of type cursor, but I get a compilation error: 'PLS-00103: Encountered the symbol "" when expecting one of the following ...'. What am I doing wrong, can you supply a complete test script?

Usually I use code like the following to get output:

FOR cur IN (SELECT * FROM emp) LOOP
    dbms_output.put_line (cur.sal);
  END LOOP;

but your method seems smarter.

 

[ScottMattes]

you are confused because I gave it wrong. in a blank test window type

begin
open :my_cur for select * from all_tables;
end;

then at the bottom, in the grid, right click on Variables and select Scan. That should make an entry for my_cur with a type of string. change 'string' to 'cursor' and run your test. after the test is done you click on the the ... on the far right of the my_cur line.

 

[Claus Pedersen]

Thank you for the correction. Now it works.

But it still doesn't give me the results during debug, because the cursor is only available when the test script has finished.

So basically I could get the same result by running my test script in a SQL window and then execute the query SELECT * FROM all_tables, which, by the way, is the approach I am using right now.