debug.f to instrument PL/SQL code

Purpose
-------
Code Instrumentation is the fine art of making every other line of code be debug.
Here we show you how to install and implement debug.f in you PL/SQL code.

Download
--------

debugf.zip contains the code, then extract and save all files to a local folder.
http://asktom.oracle.com/~tkyte/debugf -- This URL expired,
use this one to download debugf:  https://drive.google.com/file/d/0B9XqiVdd05wXaEhDMTNYX1E5dWM/edit?usp=sharing


Discussion
----------
"Instrumentation overhead"
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:10445025326812

"The 10 (Oracle Development) Commandments"
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:51960184066540

Setup
-----
suggest you

create a utility user:

create user utility identified by utility;
grant create session, create procedure, create table, create trigger to utility;
alter user utility default tablespace users quota unlimited on users;

connect sys as sysdba
grant execute on utl_file to utility;
grant create public synonym, create any DIRECTORY to utility;

run install_debug in that schema 
create a public synonym for debug 
And then you can use it. 


connect utility/utility
@install_debug.sql
--@A:\plsql\debugf\install_debug.sql

create public synonym debug for utility.debug;
grant execute on utility.debug to public;

DROP DIRECTORY debug_dir;
CREATE or replace DIRECTORY debug_dir AS '/data1/debug';
CREATE or replace DIRECTORY debug_dir AS '/u01/debug';

CREATE or replace DIRECTORY debug_dir AS 'c:/temp/log';

--GRANT READ, WRITE ON DIRECTORY debug_dir TO PUBLIC; 
--exec debug.clear;
--exec debug.init(p_file=>'DEBUG_DIR/system.dbg');

-- Enable debug trace for current login user
declare
  ls_user varchar2(30) DEFAULT USER;
begin
 debug.clear;
 debug.init(p_file=>'DEBUG_DIR/'||ls_user||'.dbg');
 debug.status;
end;
/


-- Set module name to trace, p_modules=>''
-- Enable trace for a user, p_user=>'SYSTEM'
declare
 ls_trace_user varchar2(30) := 'CS_USER';
BEGIN
 debug.clear(p_user=>ls_trace_user);
 debug.init(p_modules=>'ALL', p_user=>ls_trace_user,
   --p_date_format=>'YYYY/MM/DD HH24:MI:SS',
   p_show_sesid=>'YES',
   p_file=>'DEBUG_DIR/'||ls_trace_user||'.dbg');
 debug.status(p_user=>ls_trace_user);
end;
/

-- batch setup logging users
declare
 ls_trace_user ct_type;
BEGIN

 ls_trace_user := ct_type ('SYS','SYSTEM','ABEDBA','ABELISTING');

 FOR i IN ls_trace_user.FIRST .. ls_trace_user.LAST
 LOOP
  debug.clear(p_user=>ls_trace_user(i));
  debug.init(p_modules=>'ALL', p_user=>ls_trace_user(i),
    --p_date_format=>'YYYY/MM/DD HH24:MI:SS',
    p_show_sesid=>'NO',
    p_file=>'DEBUG_DIR/'||ls_trace_user(i)||'.dbg');
  debug.status(p_user=>ls_trace_user(i));
 End Loop;
end;
/

Demo 1
------

Begin
  debug.f('start %s : %s', 1, sysdate);
  debug.f(SubStr(TO_CHAR(SQLCODE)||': '||SQLERRM,1,100));
Exception
    WHEN OTHERS THEN
      debug.f(SubStr(TO_CHAR(SQLCODE)||': '||SQLERRM,1,100));
End;
/

Demo 2
------

@connect "/ as sysdba"
grant execute on dbms_lock to ops$tkyte;
@connect /

set echo on
/*
first we'll start by enabling debug - this would be done once, sets up a 
table with values...

After that, we'll watch the debug....
*/
exec debug.init
exec debug.f( 'hello world' );
pause
set define off
host xterm -e tail -f /tmp/OPS\$TKYTE.dbg &
set define on

create or replace procedure want_to_trace
as
begin
    for i in 1 .. 10
    loop
        if ( mod(i,2) = 0 )
        then
            debug.f( 'processing step %s of 10 - %s', i , 'odd');
            dbms_lock.sleep(1);
        else
            debug.f( 'processing step %s of 10', i );
            dbms_lock.sleep(1);
        end if;
    end loop;
end;
/

exec want_to_trace

monitor
-------

To trace it:
tail -f [DEBUG_DIR]/[LOGIN USER].dbg
For example:
tail -f /data1/debug/SYSTEM.dbg