Re: Call DBMS_TRACE from PL/SQL

Richard,

I have compiled all my procedures with the DEBUG option.

After some more tests, it seems that the problem comes from the TRACE_LEVEL parameter.

I only want to trace exceptions (to be able to log the line nbr where an exception was raised).

It works when the trace_level is set to "trace_all_calls" but not when it's set to "trace_all_exceptions" or "trace_enabled_exceptions".

Anyway, thanks for your time !

Philippe

"Richard Spee" <rhpspee_at_wxs.nl> wrote in message news:adkccg$3kj$1_at_reader07.wxs.nl...
> Your question was very clear.
> The answer to your question is that you have to COMPILE DEBUG the pl/sql
program you want to trace.
>
> delete from sys.plsql_trace_runs;
> delete from sys.plsql_trace_events;
> commit;
>
> CREATE OR REPLACE PROCEDURE aProcedure
> IS
> BEGIN
> dbms_trace.set_plsql_trace(1);
> dbms_output.put_line('Test dbms_trace');
> dbms_trace.clear_plsql_trace;
> END;
> /
> select count(*) from sys.plsql_trace_runs; -- still 0
> select count(*) from sys.plsql_trace_events; -- still 0
> alter procedure aProcedure compile debug;
> execute aProcedure;
> select count(*) from sys.plsql_trace_runs;
> select count(*) from sys.plsql_trace_events;
>
>
> "Philippe Gaudin" <NOSPAM_pga_at_bsb.com> wrote in message
> news:BJhL8.5$pF2.1650_at_nreader2.kpnqwest.net...
> > 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 - 02:16:02 CDT