In this post we would see a practical example of Oracle instrumentation.
This example shows the instrumenting mechanism of debug.f across sessions and across schema
First we have to download and install debug.f from
http://gerardnico.com/wiki/database/oracle/debugf
which is probably the only place you might find the debug.f code.
The different procedures available for the debug purpose are
1)debug.init('ALL','C:\debugf123\temp\test.txt','SYSTEM','YES','DD/MM/YYYY HH24:MI:SS',30,'YES');
This is used to intialize the debug the first parameter 'ALL' meaning all modules(can be function,procedure or package etc) and SYSTEM meaning the schema i want to debug
2)debug.f('the first is %s',v_word1);
This one works like printf in C and the maximum you can give is 10 parameters where v_word1 is a parameter
3)debug.fa('the third is %s and %s',debug.argv(v_word1,v_amount));
This is same as debug.f but here you can give more than 10 parameters
4)debug.clear :-This is used to stop the debug.
The sample function which i have taken and modified to be instrumented is available at http://www.shareoracleapps.com/search/label/Workflow.This site offers very good information about financial transactions and also contains code for general bank operation which would be helpful if you want to try creating a basic bank application as test project.
The sample function ruppee_to_word is
CREATE OR REPLACE FUNCTION ruppee_to_word (amount IN NUMBER) RETURN VARCHAR2 AS
v_length INTEGER := 0;
v_num2 VARCHAR2 (50) := NULL;
v_amount VARCHAR2 (50) := TO_CHAR (TRUNC (amount));
v_word VARCHAR2 (4000) := NULL;
v_word1 VARCHAR2 (4000) := NULL;
TYPE myarray IS TABLE OF VARCHAR2 (255);
v_str myarray := myarray (' Thousand ', ' Lakh ', ' Crore ');
BEGIN
debug.f('the entered time %s',sysdate);
IF ((amount = 0) OR (amount IS NULL)) THEN
v_word := 'zero';
ELSIF (TO_CHAR (amount) LIKE '%.%')
THEN
IF (SUBSTR (amount, INSTR (amount, '.') + 1) > 0)
THEN v_num2 := SUBSTR (amount, INSTR (amount, '.') + 1);
IF (LENGTH (v_num2) < 2)
THEN v_num2 := v_num2 * 10;
END IF;
v_word1 := ' AND ' || (TO_CHAR (TO_DATE (SUBSTR (v_num2, LENGTH (v_num2) - 1, 2), 'J'),'JSP')) || ' paise ';
debug.f('the first is %s',v_word1);
v_amount := SUBSTR (amount, 1, INSTR (amount, '.') - 1);
debug.fa('the second is %s and %s',debug.argv(v_word1,v_amount));
v_word := TO_CHAR (TO_DATE (SUBSTR (v_amount, LENGTH (v_amount) - 2, 3),'J'),'Jsp') || v_word;
v_amount := SUBSTR (v_amount, 1, LENGTH (v_amount) - 3);
debug.fa('the third is %s and %s',debug.argv(v_word1,v_amount));
FOR i IN 1 .. v_str.COUNT
LOOP
EXIT WHEN (v_amount IS NULL);
v_word :=
TO_CHAR (TO_DATE (SUBSTR (v_amount, LENGTH (v_amount) - 1,
2),
'J'), 'Jsp' ) || v_str (i) || v_word; v_amount := SUBSTR (v_amount, 1, LENGTH (v_amount) - 2);
END LOOP;
END IF;
ELSE
v_word := TO_CHAR (TO_DATE (TO_CHAR (amount, '999999999'), 'J'), 'JSP');
END IF;
v_word := v_word || ' ' || v_word1 || ' only ';
v_word := REPLACE (RTRIM (v_word), ' ', ' ');
v_word := REPLACE (RTRIM (v_word), '-', ' ');
debug.f('the entered time %s and the word is %s',sysdate,v_word);
RETURN INITCAP (v_word);
END ruppee_to_word;
This example shows the instrumenting mechanism of debug.f across sessions and across schema
First we have to download and install debug.f from
http://gerardnico.com/wiki/database/oracle/debugf
which is probably the only place you might find the debug.f code.
The different procedures available for the debug purpose are
1)debug.init('ALL','C:\debugf123\temp\test.txt','SYSTEM','YES','DD/MM/YYYY HH24:MI:SS',30,'YES');
This is used to intialize the debug the first parameter 'ALL' meaning all modules(can be function,procedure or package etc) and SYSTEM meaning the schema i want to debug
2)debug.f('the first is %s',v_word1);
This one works like printf in C and the maximum you can give is 10 parameters where v_word1 is a parameter
3)debug.fa('the third is %s and %s',debug.argv(v_word1,v_amount));
This is same as debug.f but here you can give more than 10 parameters
4)debug.clear :-This is used to stop the debug.
The sample function which i have taken and modified to be instrumented is available at http://www.shareoracleapps.com/search/label/Workflow.This site offers very good information about financial transactions and also contains code for general bank operation which would be helpful if you want to try creating a basic bank application as test project.
The sample function ruppee_to_word is
CREATE OR REPLACE FUNCTION ruppee_to_word (amount IN NUMBER) RETURN VARCHAR2 AS
v_length INTEGER := 0;
v_num2 VARCHAR2 (50) := NULL;
v_amount VARCHAR2 (50) := TO_CHAR (TRUNC (amount));
v_word VARCHAR2 (4000) := NULL;
v_word1 VARCHAR2 (4000) := NULL;
TYPE myarray IS TABLE OF VARCHAR2 (255);
v_str myarray := myarray (' Thousand ', ' Lakh ', ' Crore ');
BEGIN
debug.f('the entered time %s',sysdate);
IF ((amount = 0) OR (amount IS NULL)) THEN
v_word := 'zero';
ELSIF (TO_CHAR (amount) LIKE '%.%')
THEN
IF (SUBSTR (amount, INSTR (amount, '.') + 1) > 0)
THEN v_num2 := SUBSTR (amount, INSTR (amount, '.') + 1);
IF (LENGTH (v_num2) < 2)
THEN v_num2 := v_num2 * 10;
END IF;
v_word1 := ' AND ' || (TO_CHAR (TO_DATE (SUBSTR (v_num2, LENGTH (v_num2) - 1, 2), 'J'),'JSP')) || ' paise ';
debug.f('the first is %s',v_word1);
v_amount := SUBSTR (amount, 1, INSTR (amount, '.') - 1);
debug.fa('the second is %s and %s',debug.argv(v_word1,v_amount));
v_word := TO_CHAR (TO_DATE (SUBSTR (v_amount, LENGTH (v_amount) - 2, 3),'J'),'Jsp') || v_word;
v_amount := SUBSTR (v_amount, 1, LENGTH (v_amount) - 3);
debug.fa('the third is %s and %s',debug.argv(v_word1,v_amount));
FOR i IN 1 .. v_str.COUNT
LOOP
EXIT WHEN (v_amount IS NULL);
v_word :=
TO_CHAR (TO_DATE (SUBSTR (v_amount, LENGTH (v_amount) - 1,
2),
'J'), 'Jsp' ) || v_str (i) || v_word; v_amount := SUBSTR (v_amount, 1, LENGTH (v_amount) - 2);
END LOOP;
END IF;
ELSE
v_word := TO_CHAR (TO_DATE (TO_CHAR (amount, '999999999'), 'J'), 'JSP');
END IF;
v_word := v_word || ' ' || v_word1 || ' only ';
v_word := REPLACE (RTRIM (v_word), ' ', ' ');
v_word := REPLACE (RTRIM (v_word), '-', ' ');
debug.f('the entered time %s and the word is %s',sysdate,v_word);
RETURN INITCAP (v_word);
END ruppee_to_word;
I have created the package debug and function rupee_to_word on SYSTEM user and given the necessary priviledges to other users where the debug package can be used for instrumentation
I have then created three separate sessions to show that we can initialize debug across session and get the information about other sessions.
The first session:-
DECLARE
n VARCHAR2(4000);
l_amount NUMBER:=1234;
BEGIN
debug.init('ALL','C:\debugf123\temp\test.txt','SYSTEM','YES','DD/MM/YYYY HH24:MI:SS',30,'YES');
n:=ruppee_to_word(l_amount);
debug.f('The Translation of Rupee %s is %s',l_amount,n);
END;
The second session:-
DECLARE
n VARCHAR2(4000);
l_amount NUMBER:=2132.43;
BEGIN
n:=ruppee_to_word(l_amount);
debug.f('The Translation of Rupee %s is %s',l_amount,n);
END;
the third session
DECLARE
n VARCHAR2(4000);
l_amount NUMBER:=456;
BEGIN
debug.init('ALL','C:\debugf123\temp\test.txt','SYSTEM','YES','DD/MM/YYYY HH24:MI:SS',30,'YES');
n:=ruppee_to_word(l_amount);
debug.f('The Translation of Rupee %s is %s',l_amount,n);
debug.clear;
END;
I had also created one more session in ATM schema to show that debugging can be done across schema and run the debug initilation in SYSTEM Schema as
DECLARE
BEGIN
debug.init('ALL','C:\debugf123\temp\test.txt','ATM','YES','DD/MM/YYYY HH24:MI:SS',30,'YES');
END;
Then called the below block in ATM schema
DECLARE
n VARCHAR2(4000);
l_amount NUMBER:=456;
BEGIN
n:=ruppee_to_word(l_amount);
END;
Please find the logged data for all the sessions below
Debug parameters initialized on 20-SEP-2011 22:20:43
USER: SYSTEM
MODULES: ALL
FILENAME: C:\debugf123\temp\test.txt
SHOW DATE: YES
DATE FORMAT: DD/MM/YYYY HH24:MI:SS
NAME LENGTH: 30
SHOW SESSION ID: YES
Session_id Data time Owner.procedure logged message
600 - 20/09/2011 22:20:43( SYSTEM.RUPPEE_TO_WORD 10) the entered time 20-SEP11
600 - 20/09/2011 22:20:43( SYSTEM.RUPPEE_TO_WORD 45) the entered time 20-SEP-11 and the word is ONE THOUSAND TWO HUNDRED THIRTY FOUR only
600 - 20/09/2011 22:20:43( SYSTEM.ANONYMOUS BLOCK 7) The Translation of Rupee 1234 is One Thousand Two Hundred Thirty Four Only
601 - 20/09/2011 22:21:10( SYSTEM.RUPPEE_TO_WORD 10) the entered time 20-SEP-11
601 - 20/09/2011 22:21:10( SYSTEM.RUPPEE_TO_WORD 23) the first is AND FORTY-THREE paise
601 - 20/09/2011 22:21:10( SYSTEM.RUPPEE_TO_WORD 25) the second is AND FORTY-THREE paise and 2132
601 - 20/09/2011 22:21:10( SYSTEM.RUPPEE_TO_WORD 28) the third is AND FORTY-THREE paise and 2
601 - 20/09/2011 22:21:10( SYSTEM.RUPPEE_TO_WORD 45) the entered time 20-SEP-11 and the word is Two Thousand One Hundred Thirty Two AND FORTY THREE paise only
601 - 20/09/2011 22:21:10( SYSTEM.ANONYMOUS BLOCK 7) The Translation of Rupee 2132.43 is Two Thousand One Hundred Thirty Two And Forty Three Paise Only
602 - 20/09/2011 22:21:24( SYSTEM.RUPPEE_TO_WORD 10) the entered time 20-SEP-11
602 - 20/09/2011 22:21:24( SYSTEM.RUPPEE_TO_WORD 45) the entered time 20-SEP-11 and the word is FOUR HUNDRED FIFTY SIX only
602 - 20/09/2011 22:21:24( SYSTEM.ANONYMOUS BLOCK 7) The Translation of Rupee 456 is Four Hundred Fifty Six Only
Debug parameters initialized on 20-SEP-2011 22:25:17
USER: ATM
MODULES: ALL
FILENAME: C:\debugf123\temp\test.txt
SHOW DATE: YES
DATE FORMAT: DD/MM/YYYY HH24:MI:SS
NAME LENGTH: 30
SHOW SESSION ID: YES
611 - 20/09/2011 22:25:17( SYSTEM.RUPPEE_TO_WORD 10) the entered time 20-SEP-11
611 - 20/09/2011 22:25:17( SYSTEM.RUPPEE_TO_WORD 45) the entered time 20-SEP-11 and the word is FOUR HUNDRED FIFTY SIX only
611 - 20/09/2011 22:25:17( ATM.ANONYMOUS BLOCK 7) The Translation of Rupee 456 is Four Hundred Fifty Six Only
Debug parameters initialized on 20-SEP-2011 23:10:17
USER: ATM
MODULES: ALL
FILENAME: C:\debugf123\temp\test.txt
SHOW DATE: YES
DATE FORMAT: DD/MM/YYYY HH24:MI:SS
NAME LENGTH: 30
SHOW SESSION ID: YES
617 - 20/09/2011 23:10:31( SYSTEM.RUPPEE_TO_WORD 10) the entered time 20-SEP-11
617 - 20/09/2011 23:10:31( SYSTEM.RUPPEE_TO_WORD 45) the entered time 20-SEP-11 and the word is FOUR HUNDRED FIFTY SIX only
