Problems using dbms_trace
I created the following function which
almost works. The problem is that I would
like to return the name of the file being
created for this trace. Therefore I can
call it from my application and easily
track down the file that I am looking for.
The problem is that the supplied function DBMS_TRACE.get_plsql_trace_runnumber does
not work. Unless I am missing something.
It always returns a 0. I was hoping to
return either the directory and filename
or at least just the number of the tracing
session. I have a procedure that I am going
to use to stop tracing.
Any help would be appreciated.
Code:
CREATE OR REPLACE FUNCTION
--' $Header:
--' Oracle supplied tracing levels. More than one can be used.
--' trace_all_calls constant integer := 1; -- Trace calls/returns
--' trace_enabled_calls constant integer := 2;
--' trace_all_exceptions constant integer := 4; -- trace exceptions
--' trace_enabled_exceptions constant integer := 8; -- (and handlers)
--' trace_all_sql constant integer := 32; -- trace SQL statements
--' trace_enabled_sql constant integer := 64; -- at PL/SQL level (does not invoke SQL trace)
--' trace_all_lines constant integer := 128; -- trace each line
--' trace_enabled_lines constant integer := 256;
p_enable_sql_trace(p_trace_level BINARY_INTEGER DEFAULT DBMS_TRACE.trace_all_calls)
RETURN BINARY_INTEGER
AS
v_trace_file VARCHAR2(200);
v_tracenumber BINARY_INTEGER;
BEGIN
--' start trace data dumping in session
--' the parameter is the sum of the above constants representing which
--' events to trace
DBMS_TRACE.set_plsql_trace(p_trace_level);
v_tracenumber := DBMS_TRACE.get_plsql_trace_runnumber;
--' Return the run-number
v_trace_file := 'The number of the trace file is: '||TO_CHAR(v_tracenumber);
RETURN v_trace_file;
END p_enable_sql_trace;
/