Call DBMS_TRACE from PL/SQL

From: Philippe Gaudin <NOSPAM_pga_at_bsb.com>
Date: Tue, 04 Jun 2002 14:20:47 GMT
Message-ID: <314L8.43$Sv2.5252@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
pga_at_bsb.com Received on Tue Jun 04 2002 - 09:20:47 CDT