Tracing execution of queries

[Dick Campbell]

It looks to me like you can't trace the execution of queries. The trace/debug skips over any query-like lines of code and if I set a break point within a query I get messaging saying that it is an illegal place for a break point.

Is this correct or do I need to change a setting somewhere? Obviously debugging within a query will loop through every row (potentially millions) so I understand why this might be so.

Dick Campbell

 

[Joachim Rupik]

Hello,

You can debug only a PL/SQL code, so you should define a SQL query as a cursor, open it, and do fetches within PL/SQL loop. And such code you will be able to debug.

Please look at the example below:

DECLARE
my_record emp%ROWTYPE;
CURSOR c1 IS SELECT * FROM emp;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO my_record;
EXIT WHEN c1%NOTFOUND;
-- process data record
dbms_output.put_line(my_record.ename);
END LOOP;
END;

or that way:

DECLARE
cursor cur_2 is select ename from emp order by ename;
BEGIN
for i in cur_2
loop
dbms_output.put_line(i.ename);
end loop;
end;

Greetings,
Joachim Rupik

 

[Dick Campbell]

Thanks, I have been looking at various other threads and came to the same conclusion.