Debugging a PL/SQL Stored procedure

Wednesday, March 15, 2017 plsql , sql 46 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

Seach a string and replace consequent lines in perl

Tuesday, March 07, 2017 0 Comments

Lets say I have C/C++  file which has some pattern in the line at the start. I also know that there will be 2 lines following the line that will match my pattern. I want to remove these three lines and add a new line which has a different string which is nothing but I want to replace a all those 3 three lines with a different line.

MTTRACE("ARG1",
      "ARG2",
     "ARG3");

//some code follows
MTTRACE("ARG1",
      "ARG2",
     "ARG3");

New fIle should look  as below:

MYTRACE(ARG);
//some code follows
MYTRACE(ARG);

Solution:

perl -pe '$x=3  if /^\s*MTTRACE.*/;
if($x>0){$x-- and undef $_;$_="MYTRACE(ARG);\n" if $x==0}' myfile.cc