Re: dbms_output.put_line in a database trigger

"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message news:<toaik9pgiikod9_at_news.demon.nl>...
> "Joe Bo" <parker_at_indiamail.com> wrote in message
> news:a8cf5098.0108230915.1efb2c8c_at_posting.google.com...
> > I have a database trigger on table a , which inserts records into
> > table b, which in turn has a trigger which inserts into table c, which
> > once again has a trigger which inserts into table d.
> >
> > Now I want to know if I can include calls to dbms_output.put_line, in
> > all the 3 triggers , and know where the operation is at a partiticular
> > given point. i.e. if the process is in trigger on table a then i will
> > display 'CURRENTLY EXECUTING TRIGGER ON TABLE A', and then the same
> > with the other 2 triggers.
> >
> > Is this possible .
>
> Yes this is possible, but if you are not running this in sql*plus your
> output goes just nowhere.

Well, it goes to the buffer on the server side. After trigger(s) completes you can use dbms_output.get_line to retrieve the output. Sqlplus does that for you if 'serveroutput' is enabled.

> You should look into either using the dbms_application_info package, which
> you can use to set the application info column of v$session,

It will be difficult to catch changes in v$session.[module/action/client_info]. If all you need is just to get debug info _after_ stored procedure/trigger finishes, dbms_output works fine. If you need something more 'real-time' :) dbms_pipe is a good option.

> or the dbms_debug package.
>
> Hth,
>
> Sybrand Bakker, Senior Oracle DBA

Regards,
Igor. Received on Thu Aug 23 2001 - 21:22:17 CDT