Re: Call DBMS_TRACE from PL/SQL

You have created the trace-tables with /rdbms/admin/tracetab.sql For testing purposes I created a procedure with name aProcedure. You have to replace it with the name of your application.

In Sqlplus:

select count(*) from sys.plsql_trace_runs; select count(*) from sys.plsql_trace_events;

execute dbms_trace.set_plsql_trace(1);
alter procedure aProcedure compile debug; -- this is probably the step you forgot

execute aProcedure('table_name','asc');

execute dbms_trace.clear_plsql_trace;

select count(*) from sys.plsql_trace_runs; select count(*) from sys.plsql_trace_events;

Be careful. The events table can grow very fast

"Philippe Gaudin" <NOSPAM_pga_at_bsb.com> wrote in message news:8%3L8.42$Sv2.5310_at_nreader2.kpnqwest.net...
> Hi !
>
> I'm trying to set DBMS_TRACE on from PL/SQL using :
> DBMS_TRACE.set_plsql_trace (DBMS_TRACE.trace_all_exceptions);
>
> But... it doesn't seem to work (on 8.1.7.3) as I don't see the exception
> events in PLSQL_TRACE_EVENTS.
>
> Here are the procedures used for testing (traceexc was inspired by a script
> found at http://asktom.oracle.com ):
>
> --==================================================
> CREATE OR REPLACE PROCEDURE traceexc AS
> l_runid NUMBER;
> BEGIN
> SELECT sys.plsql_trace_runnumber.CURRVAL
> INTO l_runid
> FROM DUAL;
>
> FOR x IN (SELECT *
> FROM sys.plsql_trace_events
> WHERE runid = l_runid AND event_kind =
> DBMS_TRACE.plsql_trace_excp_raised
> ORDER BY event_seq DESC) LOOP
> DBMS_OUTPUT.put_line (
> 'Exception '
> || x.excp
> || '('
> || SQLERRM (SQLCODE)
> || ') occured in '
> || x.event_unit_kind
> || ' '
> || x.event_unit_owner
> || '.'
> || x.event_unit
> || ' at line '
> || x.event_line
> );
> EXIT;
> END LOOP;
> END;
> --==================================================
> CREATE OR REPLACE PROCEDURE testexc AS
> BEGIN
> EXECUTE IMMEDIATE 'insert into tototata values (1,12)'; -- Will raise an
> exception
> EXCEPTION
> WHEN OTHERS THEN
> traceexc;
> raise;
> END testexc;
> --==================================================
> CREATE OR REPLACE PROCEDURE test AS
> BEGIN
> DBMS_TRACE.set_plsql_trace (DBMS_TRACE.trace_all_exceptions);
> testexc;
> END test;
> --==================================================
>
>
> Any idea on how to enable DBMS_TRACE from pl/sql ?
>
> Thanks in advance !
>
> Philippe
>
>
>
>
>
Received on Tue Jun 04 2002 - 14:09:38 CDT