JL Computer Consultancy

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;

.