Wish I could make things bold in my response instead
of simple text ( to highlight changes)
Features added:
F1. It has a flag to enable and disable debugging.
The flag take on following values (int constants)
a. DEBUG_OFF - This means that debugging code for
debug.f() and debug.fa() would just return
without doing anything.
b. DUMP_FOR_URL_ONLY - enable debugging so that you
see the output only in the URL displayed -
*DON'T* do any trace file generation in the trace
file directory.
c. DUMP_IN_TRACE_FILES_ONLY
enable debugging so that you see the output only in
the trace file directory - no need to display the
output in the URL.
d. DUMP_FOR_TRACE_FILES_AND_URL
enable debugging so that you see the output the
trace file as well as on the URL.
To disable debugging execute
debug.disable;
To enable debugging (e.g. for trace files and URL)
execute
debug.enable ( DUMP_FOR_TRACE_FILES_AND_URL );
For URL based dumping (which is not htp.p based),
you can do the following
1. Before URL based generation code begins check
to see from the URL page property if debug flag
is on.
2. If the flag is off don't do anything
3. If the flag is on, execute debug.enable (
valid_debug_flag)
4. continue processing URL generation code - this
could involve plsql code calls that have been
instrumented using debug.f.
5. if debug_flag is enabled for URL display
execute debug.get_debug_info_flush to get
the data and flush the temporary table where
it gets stored.
6. execute debug.disable (this is to reset the
package variable)
F2. If debug is not set to off then the output is
always shown using dbms_output.put_line. This is
useful for sql prompt based debugging which is
what many people are used to.
Note: I added a temporary table to store messages
to be displayed in URL.
-----------------------debug.sql starts -----------
---- also contains schema required to make it work
---------------------------------------------------
set echo off
create or replace
type Argv as
table of varchar2(4000);
/
create global temporary table debug_info
(
message varchar2(4000)
)
on commit delete rows;
/
create table debugTab(
userid varchar2(30) primary key,
modules varchar2(4000),
locat varchar2(4000),
filename varchar2(4000)
)
/
create or replace
trigger bi_fer_debugtab
before insert on debugtab for each row
begin
:new.modules := upper( :new.modules );
end;
/
create or replace
package debug as
--
-- version 1.0
-- clbeck - 13-OCT-98 - Initial version
--
-- PACKAGE TO DUMP DEBUG INFORMATION OF PL/SQL ROUTINE
-- TO A FILE DURING EXECUTION
--
--
-- This package allows the developer to selectively produce debug
-- information for pl/sql process.
--
-- Setup:
-- Make sure the utl_file_dir paramter is assigned in the init.ora
-- file. You need an entry for each dir that you want to be able to
-- write to.
-- eg.
-- utl_file_dir = /tmp
-- utl_file_dir = /home/clbeck/sql/debug
--
-- Usage:
-- There are two procedure to write debug information ( f and fa ).
-- Anywhere in your code that you want to print debug information use:
--
-- debug.f( 'Expected %s bytes, got %s bytes', l_expect, l_got );
--
-- This will replace the the first %s with the value of l_expect and the
-- second %s with the value of l_got.
--
-- If you have more than 10 %s in your string then you will need to use the fa
-- procedure like:
--
-- debug.fa( 'List: %s,%s,%s,%s,%s,%s',
-- argv( 1, 2, l_num, 'Chris', l_cnt, 10 ) );
--
-- Runtime:
-- To enable the debug run:
--
-- debug.init( 'myProc' );
-- debug.enable ( <a valid debug flag other than debug.DEBUG_OFF> )
--
-- This will cause only debug for the procedure/package named
-- myProc to be generated.
-- All other debug statements will generate no output.
-- To debug all procedures/packages,
-- set p_modules = 'ALL';
--
-- NOTE:
-- If debug is not set to off then the output is
-- always shown using dbms_output.put_line. This is
-- useful for sql prompt based debugging which is
-- what many people are used to.
--
-- To stop debug run:
--
-- debug.clear;
--
-- To disable debug run
--
-- debug.disable;
--
-- To get debug message outputted for a URL generation do the following:
--
-- For URL based dumping (which is not htp.p based),
-- you can do the following
-- 1. Before URL based generation code begins check
-- to see from the URL page property if debug flag
-- is on.
-- 2. If the flag is off don't do anything
-- 3. If the flag is on, execute debug.enable (
-- valid_debug_flag)
-- 4. continue processing URL generation code - this
-- could involve plsql code calls that have been
-- instrumented using debug.f.
-- 5. if debug_flag is enabled for URL display
-- execute debug.get_debug_info_flush to get
-- the data and flush the temporary table where
-- it gets stored.
-- 6. execute debug.disable (this is to reset the
-- package variable)
--
-- Output:
-- The output looks like:
--
-- 981013 130530 (CLBECK.MYPROC, 221) this is my debug output
-- ^^^^^^ ^^^^^^ ^^^^^^^^^^^^^ ^^^ ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
-- date time owner.proc lineno message
--
--
-- Enhancements and Bugs:
--
-- Send all enhancements requests and bugs to me,
-- Christopher Beck (clbeck@us.oracle.com)
--
--
g_dir_locat constant varchar2(4000) := '/tmp';
-- following constants determine where all the debugging messages
-- are output. Please note that if the DEBUG_OFF is NOT used then
-- regardless of the output option chosen, the message is output
-- on the sqlplus screen using dbms_output.put_line.
DEBUG_OFF constant int := 0; -- no debugging messages output to
-- any destination
DUMP_FOR_URL_ONLY constant int := 1; -- only for URL based debugging
DUMP_IN_TRACE_FILES_ONLY constant int := 2; --dump only in trace files
DUMP_FOR_TRACE_FILES_AND_URL constant int := 3; -- both in URL and trace files
emptyDebugArgv Argv;
--
-- Initializes the debuging for specified p_modules and will dump the
-- output to the p_dir directory on the server for the user p_user.
--
procedure init(
p_modules in varchar2 default 'ALL',
p_dir in varchar2 default g_dir_locat,
p_file in varchar2 default null,
p_user in varchar2 default user );
procedure f(
p_message in varchar2,
p_arg1 in varchar2 default null,
p_arg2 in varchar2 default null,
p_arg3 in varchar2 default null,
p_arg4 in varchar2 default null,
p_arg5 in varchar2 default null,
p_arg6 in varchar2 default null,
p_arg7 in varchar2 default null,
p_arg8 in varchar2 default null,
p_arg9 in varchar2 default null,
p_arg10 in varchar2 default null );
procedure fa(
p_message in varchar2,
p_args in Argv default emptyDebugArgv );
procedure clear(
p_user in varchar2 default user );
--
-- Returns the current status of debugging for the user p_user.
--
procedure status(
p_user in varchar2 default user,
p_modules out varchar2,
p_file out varchar2,
p_dir out varchar2 );
--
-- deletes info from the temporary table (useful for flushing the
-- messages already seen in a URL based debugging
--
procedure delete_debug_info;
--
-- enables debugging
-- messages already seen in a URL based debugging
-- The p_debug_flag can take on following values (int constants)
-- 1. DEBUG_OFF - This means that debugging code for
-- debug.f() and debug.fa() would just return
-- without doing anything.
-- 2. DUMP_FOR_URL_ONLY - enable debugging so that you
-- see the output only in the URL displayed -
-- *DON'T* do any trace file generation in the trace
-- file directory.
-- 3. DUMP_IN_TRACE_FILES_ONLY
-- enable debugging so that you see the output only in
-- the trace file directory - no need to display the
-- output in the URL.
-- 4. DUMP_FOR_TRACE_FILES_AND_URL
-- enable debugging so that you see the output the
-- trace file as well as on the URL.
--
procedure enable ( p_debug_flag int );
-- disables debugging
-- Used to incur minimal performance penalty for instrumenting
-- the code with debug.f() and debug.fa()
procedure disable;
-- gets debug messages from the temporary table (for url based
-- debugging - to display the debug messages on the URL just
-- the asktom.oracle.com does if you flip the "NO" in an article's
-- URL to "YES")
function get_debug_info return sys_refcursor;
-- gets debug messages from the temporary table and deletes
-- that info from the temporary table ( useful for url based
-- debugging - to display the debug messages on the URL just
-- the asktom.oracle.com does if you flip the "NO" in an article's
-- URL to "YES") Typically you want to see the same message again in
-- the URL - so this method deletes after returning a cursor
-- containing debug messages
function get_debug_info_flush return sys_refcursor;
end debug;
/
show error
grant execute on debug to public
/
create or replace
package body debug as
--g_enable_flag int default DEBUG_OFF;
g_enable_flag int default DEBUG_OFF;
g_owner varchar2(2000);
g_name varchar2(2000);
g_lineno number;
g_caller_t varchar2(2000);
g_file varchar2(2000);
procedure init(
p_modules in varchar2 default 'ALL',
p_dir in varchar2 default g_dir_locat,
p_file in varchar2 default null,
p_user in varchar2 default user ) is
--
r debugTab%rowtype;
begin
clear( p_user );
insert into debugTab values ( p_user, p_modules, p_dir, p_file );
end init;
procedure clear( p_user varchar2 default user ) is
begin
delete from debugTab where userid = p_user;
end clear;
procedure status(
p_user in varchar2 default user,
p_modules out varchar2,
p_file out varchar2,
p_dir out varchar2 ) is
begin
select modules, locat, filename
into p_modules, p_dir, p_file
from debugTab
where userid = p_user;
exception
when NO_DATA_FOUND then
p_modules := null;
p_dir := null;
p_file := null;
end status;
procedure who_called_me(
owner out varchar2,
name out varchar2,
lineno out number,
caller_t out varchar2 ) is
--
call_stack varchar2(4096) default dbms_utility.format_call_stack;
n number;
found_stack BOOLEAN default FALSE;
line varchar2(255);
cnt number := 0;
begin
loop
n := instr( call_stack, chr(10) );
exit when ( cnt = 3 or n is NULL or n = 0 );
--
line := substr( call_stack, 1, n-1 );
call_stack := substr( call_stack, n+1 );
--
if not found_stack then
if line like '%handle%number%name%' then
found_stack := TRUE;
end if;
else
cnt := cnt + 1;
-- cnt = 1 is ME
-- cnt = 2 is MY Caller
-- cnt = 3 is Their Caller
if ( cnt = 3 ) then
lineno := to_number(substr( line, 13, 6 ));
line := substr( line, 21 );
if ( line like 'pr%' ) then
n := length( 'procedure ' );
elsif ( line like 'fun%' ) then
n := length( 'function ' );
elsif ( line like 'package body%' ) then
n := length( 'package body ' );
elsif ( line like 'pack%' ) then
n := length( 'package ' );
else
n := length( 'anonymous block ' );
end if;
caller_t := ltrim(rtrim(upper(substr( line, 1, n-1 ))));
line := substr( line, n );
n := instr( line, '.' );
owner := ltrim(rtrim(substr( line, 1, n-1 )));
name := ltrim(rtrim(substr( line, n+1 )));
end if;
end if;
end loop;
end who_called_me;
function is_number( n varchar2 ) return boolean is
begin
if n between '0' and '9' then
return true;
end if;
return false;
end is_number;
function parse_it(
p_message in varchar2,
p_args in argv default emptyDebugArgv ) return varchar2 is
--
l_tmp long := p_message;
l_str long := null;
l_idx number;
l_numstr1 varchar2(10);
l_numstr2 varchar2(10);
l_tmp1 long;
l_str1 long;
l_num number;
l_char long;
begin
for i in 1 .. p_args.count loop
l_idx := instr( l_tmp, '%' ) ;
exit when nvl(l_idx,0) = 0;
l_str := l_str || substr( l_tmp, 1, l_idx-1 );
l_tmp := substr( l_tmp, l_idx+1 );
if substr( l_tmp, 1, 1 ) = 's' or
substr( l_tmp, 1, 1 ) = 'd' then
l_str := l_str || p_args(i);
l_tmp := substr( l_tmp, 2 );
elsif is_number( substr( l_tmp, 1, 1 ) ) or
substr( l_tmp, 1, 1 ) = '.' then
l_numstr1 := null;
l_numstr2 := null;
l_tmp1 := l_tmp;
l_str1 := l_str;
loop
exit when not is_number( substr( l_tmp1, 1, 1 ) );
l_numstr1 := l_numstr1 || substr( l_tmp1, 1, 1 );
l_tmp1 := substr( l_tmp1, 2 );
end loop;
if substr( l_tmp1, 1, 1 ) = '.' then
l_tmp1 := substr( l_tmp1, 2 );
if is_number( substr( l_tmp1, 1, 1 ) ) then
loop
exit when not is_number( substr( l_tmp1, 1, 1 ) );
l_numstr2 := l_numstr2 || substr( l_tmp1, 1, 1 );
l_tmp1 := substr( l_tmp1, 2 );
end loop;
else
l_tmp1 := '!' || l_tmp1;
end if;
end if;
begin
if substr( l_tmp1, 1, 1 ) = 's' then
l_tmp := substr( l_tmp1, 2 );
if l_numstr2 is null then
l_tmp1 := p_args(i);
else
l_tmp1 := substr( p_args(i), 1, l_numstr2 );
end if;
if l_numstr1 is not null then
l_tmp1 := lpad( l_tmp1, l_numstr1 );
end if;
l_str := l_str1 || l_tmp1;
elsif substr( l_tmp1, 1, 1 ) = 'd' then
l_tmp := substr( l_tmp1, 2 );
if l_numstr1 is null then
l_tmp1 := lpad( '9', 39, '9' );
else
l_tmp1 := lpad( '9', l_numstr1, '9' );
end if;
if l_numstr2 is not null then
l_tmp1 := substr( l_tmp1, 1, l_numstr1-l_numstr2 ) || '.' ||
substr( l_tmp1, -l_numstr2 );
end if;
l_str := l_str1 || to_char( to_number( p_args(i) ), l_tmp1 );
else
l_str := l_str || '%';
end if;
exception
when others then
l_str := l_str1 || 'XXXXXXXXXX';
end;
else
l_str := l_str || '%';
end if;
end loop;
return l_str || l_tmp;
exception
when others then
return l_str || l_tmp;
end parse_it;
procedure internal_f(
p_message in varchar2,
p_args in Argv default emptyDebugArgv ) is
--
l_locat varchar2(4000);
l_modules varchar2(4000);
l_filename varchar2(4000);
l_message long := null;
l_final_message long;
l_file utl_file.file_type;
l_date varchar2(255);
begin
select modules, locat, filename, to_char( sysdate, 'YYMMDD HH24MISS' )
into l_modules, l_locat, l_filename, l_date
from debugTab
where userid = user;
if instr( l_modules, nvl(g_name,'BLAH') ) = 0 and
l_modules <> 'ALL' then
return;
end if;
if l_filename is not null then
g_file := l_filename;
end if;
l_message := parse_it( p_message, p_args );
/*
l_message := p_message;
begin
for i in 1 .. p_args.count loop
if instr( l_message, '%s' ) = 0 then
exit;
else
l_message := substr( l_message, 1, instr( l_message, '%s' )-1 ) ||
p_args(i) ||
substr( l_message, instr( l_message, '%s' )+2 );
end if;
end loop;
exception
when others then
null;
end;
*/
l_message := replace( l_message, '\n', chr(10) );
l_message := replace( l_message, '\t', chr(9) );
if( g_enable_flag = DUMP_IN_TRACE_FILES_ONLY or
g_enable_flag = DUMP_FOR_TRACE_FILES_AND_URL ) then
l_file := utl_file.fopen( l_locat, g_file, 'a', 32767 );
if not utl_file.is_open( l_file ) then
dbms_output.put_line( 'File not opened' );
end if;
end if;
if g_owner is null then
g_owner := user;
g_name := 'ANONYMOUS BLOCK';
end if;
l_final_message := l_date ||
' (' || lpad( g_owner || '.' || g_name, 20 ) || ',' ||
lpad(g_lineno,4) || ') ' || l_message;
if( g_enable_flag = DUMP_IN_TRACE_FILES_ONLY or
g_enable_flag = DUMP_FOR_TRACE_FILES_AND_URL ) then
utl_file.put( l_file, '' );
utl_file.put_line( l_file, l_final_message );
utl_file.fclose( l_file );
end if;
if( g_enable_flag = DUMP_FOR_URL_ONLY or
g_enable_flag = DUMP_FOR_TRACE_FILES_AND_URL ) then
insert into debug_info ( message) values ( l_final_message );
end if;
-- regardless of debug flags - always show it using the
-- dbms_output.put_line for sqlplus based debugging.
dbms_output.put_line ( l_final_message );
exception
when NO_DATA_FOUND then
-- dbms_output.put_line( sqlerrm );
null;
end internal_f;
procedure fa(
p_message in varchar2,
p_args in Argv default emptyDebugArgv ) is
begin
if( g_enable_flag = DEBUG_OFF ) then
return;
end if;
who_called_me( g_owner, g_name, g_lineno, g_caller_t );
internal_f( p_message, p_args );
end fa;
procedure f(
p_message in varchar2,
p_arg1 in varchar2 default null,
p_arg2 in varchar2 default null,
p_arg3 in varchar2 default null,
p_arg4 in varchar2 default null,
p_arg5 in varchar2 default null,
p_arg6 in varchar2 default null,
p_arg7 in varchar2 default null,
p_arg8 in varchar2 default null,
p_arg9 in varchar2 default null,
p_arg10 in varchar2 default null ) is
begin
if( g_enable_flag = DEBUG_OFF ) then
return;
end if;
who_called_me( g_owner, g_name, g_lineno, g_caller_t );
internal_f( p_message,
argv( substr( p_arg1, 1, 4000 ),
substr( p_arg2, 1, 4000 ),
substr( p_arg3, 1, 4000 ),
substr( p_arg4, 1, 4000 ),
substr( p_arg5, 1, 4000 ),
substr( p_arg6, 1, 4000 ),
substr( p_arg7, 1, 4000 ),
substr( p_arg8, 1, 4000 ),
substr( p_arg9, 1, 4000 ),
substr( p_arg10, 1, 4000 ) ) );
end f;
procedure delete_debug_info is
begin
delete debug_info;
end delete_debug_info;
procedure enable ( p_debug_flag int) is
begin
if( p_debug_flag = DUMP_FOR_URL_ONLY or
p_debug_flag = DUMP_IN_TRACE_FILES_ONLY or
p_debug_flag = DUMP_FOR_TRACE_FILES_AND_URL ) then
g_enable_flag := p_debug_flag;
end if;
end enable;
procedure disable is
begin
g_enable_flag := DEBUG_OFF;
end disable;
function get_debug_info return sys_refcursor is
l_cursor sys_refcursor;
begin
open l_cursor for
select message
from debug_info;
return l_cursor;
end get_debug_info;
function get_debug_info_flush return sys_refcursor is
l_cursor sys_refcursor;
begin
l_cursor := get_debug_info;
delete_debug_info;
return l_cursor;
end get_debug_info_flush;
begin
g_file := 'DEBUGF_'||userenv('SESSIONID');
end debug;
/
show errors
/
-----------------------debug.sql ends -----------