Re: Call DBMS_TRACE from PL/SQL

Maybe it wasn't clear... my problem comes from the fact that I execute SET_PLSQL_TRACE in the body of the proceure and not from an execute statement in SQL*Plus !

Philippe

"Richard Spee" <rhpspee_at_wxs.nl> wrote in message news:adj3ms$fta$1_at_reader05.wxs.nl...
> 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 Wed Jun 05 2002 - 00:55:45 CDT