Debugging a PL/SQL Stored procedure

Wednesday, March 15, 2017 , 1 Comments

Normally while working on PL/SQL stored procedures , we use
DBMS_TRACE for knowing about the values of various variables and put some print statements.
This looks fine until you use your stored procedures on cli. But Lets assume your stored procedure is being called from a different process,lets say a c++ or a Java process. In this case you dont have  a way to send your log statements to a log file unless you have some admin permissions. But as a designer you might not have  the rights with you.

This procedure below will present you a way to put traces in your PLSQL procedure and see the values.
Below is a shell script which provides you with that provision.
Simple idea is insert our trace statements in a table.

#!/bin/ksh
#Get the filename and filepath of the input files for MD process

CONNECT_STRING=${DATABASE}

if [ $1 = "create" ];then
CRE_RESULT=`sqlplus -s ${CONNECT_STRING} << EOF
create table DEBUG_MESSAGES(
 current_date DATE,
 message varchar2(500)
);

CREATE OR REPLACE PROCEDURE DEBUG_OUTPUT( p_text IN VARCHAR2 ) AS
BEGIN
      insert into DEBUG_MESSAGES (current_date,message)
      values (sysdate, p_text);
      commit;
END;

exit;
EOF`

echo $CRE_RESULT
fi

if [ $1 = "clean" ];then
CLN_RESULT=`sqlplus -s ${CONNECT_STRING} << EOF
drop table DEBUG_MESSAGES;
drop procedure DEBUG_OUTPUT;
exit;
EOF`

echo $CLN_RESULT

fi
Now You can add your trace statements inside your procedure like below:
 DEBUG_OUTPUT('Hello World!');
 DEBUG_OUTPUT('Hello World!'||vMyVariable);
After you execute your procedure , your trace statements will be available in the table DEBUG_MESSAGES . You can go there and check

1 comment:

  1. Great Article.. Thanks for the Tips on Debugging PL/SQL procedure.

    ReplyDelete