JL Computer Consultancy

SQL_TRACE controlled.

January 1998


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

Back to Main Index of Topics.


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;

Back to Main Index of Topics.