dbms_output.put_line

[Robert Taylor]

I have a trigger that I'm trying to debug using the test window but I cannot seem to get the following things to work for me:

1. dbms_output.put_line - is there a way to get the results to display during execution and not have to wait until the trigger has completely executed to see the results?
2. How do I get the values for :NEW and :OLD to display during an INSERT on a trigger?

I know I must be doing something wrong. Any help would be appreciated.

Thank you.

 

[RobertK]

#1 why doyou need that ?
- sounds like that trigger is taking WAY too long
- old is all NULL in an insert trigger.

 

[William Robertson]

Originally posted by Robert Taylor:
1. dbms_output.put_line - is there a way to get the results to display during execution and not have to wait until the trigger has completely executed to see the results?

Perhaps you could declare a local procedure to do the PUT_LINEs? The debugger would step into that.

 

[Marco Kalter]

The :NEW and :OLD variables can indeed not be displayed by the debugger. You will have to assign the individual field values to a local variable to see them:

declare
  l_emp emp%rowtype;
begin
  l_emp.empno := :new.empno;
  l_emp.ename := :new.ename;
  ...
end;

 

[Robert Taylor]

Thank you.

 

[F. van Boven]

What's the exact solution to the 1st one...

Got a large script and I would like to follow the progress by checking the output...

 

[Worker]

I don't think there is a solution when using dbms_output. You may want to look into the dbms_application_info package though. It allows you to give "output" that is immediately visible in v$session.

 

[DoDo]

Robert,
I use "autonomous transaction" procedure to put test values into test table as a workaround for "dbms_output" or "dmbs_alert" packages.

Marco,
Can you make the :NEW and :OLD variables to be displayed by the debugger?
(add to http://www.allroundautomations.com/ubb/ultimatebb.php?ubb=get_topic;f=3;t=004116 )

 

[Stew Stryker]

I've successfully used DBMS_PIPE package for this type of debugging, which allows you to send out messages while the tested procedure is running. The Oracle documentation on it's pretty good.

 

[til]

I like to use pipes also for debugging in some cases. Especially when i have trouble reproducing the problem without client app.