Have you ever been in the position where you would like to have one of the modules in an overnight batch run with SQL_TRACE switched on, but found that the in-house mechanisms of version control and release management make it impossible for you to slip a quick 'alter session set sql_trace true' into place ?
I whipped up the following package a little while ago to help a client who had this problem. It meant that he had to introduce a couple of PL/SQL calls to critical packages over time, but natural evolution meant that this didn't actually take too long.
The script below creates a table, and a package. The table lists programs where you want sql_trace switched on, the package defines procedures that check the table. Typically you would put a start_trace at the top of a program, and a stop_trace at the bottom - you may, however, want to put a few extra start_trace calls in mid-program in case a called subprogram has been instrumented to switch tracing off.
One trap - the usual problems with mixing packages, privileges and roles - for this package to work, the owner of the package needs to receive the privilege 'alter session' granted explicitly to the id.
The code is okay for newer versions of Oracle 7 and Oracle 8
rem
rem Script: c_trace.sql
rem Author: Jonathan Lewis
rem Dated: 6th Jan 1998
rem Purpose: Allow dynamic selection of scripts to trace
rem
rem Usage (from SQL*Plus):
rem start c_trace One-off to build table and procedures
rem
rem Inside PL/SQL procedures
rem execute program_trace.start_trace('prog','type');
rem execute program_trace.stop_trace;
rem
rem start_trace will switch on sql tracing only if
rem the program and type can be found in the table
rem
rem stop_trace will unconditionally stop sql_trace
rem
rem The package is executable by PUBLIC
rem There is a public synonym for the package
rem
rem Special note:
rem For this to work, the package owner must have been directly granted the
rem the privilete to 'alter sesion' - logged in as a dba you do:
rem grant alter session to {username};
rem
rem Note: Only the owner is allowed to put program names into the table
rem If you want to change this, extra procedures would be best, e.g.
rem program_trace.add_program('prog','type');
rem program_trace.remove_program('prog','type');
rem
rem If the procedures fail, then nothing happens (all exceptions
rem are caught and nulled out).
rem
rem The name and type should be inserted into the table in CAPITALS,
rem but the procedure may be called using lower case.
rem
drop table programs_to_trace; create table programs_to_trace ( name varchar2(20) constraint ptt_ck_name_upper check (name = upper(name)), type varchar2(3) constraint ptt_ck_type_upper check (type = upper(type)), constraint ptt_pk primary key (name,type) ) ;
create or replace package program_trace as procedure start_trace( i_name varchar2, i_type varchar2 );
procedure stop_trace; end; . /
create or replace package body program_trace as procedure start_trace( i_name varchar2, i_type varchar2 ) is
m_count number; begin select count(*) into m_count from programs_to_trace where name = upper(i_name) and type = upper(i_type) ;
if (m_count != 0) then sys.dbms_session.set_sql_trace (true); end if;
exception when others then null; end;
procedure stop_trace is begin sys.dbms_session.set_sql_trace (false); exception when others then null; end ;
end ; . / drop public synonym program_trace; create public synonym program_trace for program_trace; grant execute on program_trace to public;