Re: Call DBMS_TRACE from PL/SQL

From: Richard Spee <rhpspee_at_wxs.nl>
Date: Wed, 5 Jun 2002 08:43:52 +0200
Message-ID: <adkccg$3kj$1@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 - 01:43:52 CDT