SQLPlus Debug and VIM Find/Replace

Yesterday I was asked to help to resolve some issues for a piece of PL/SQL codes, which was provided by our client support. I was working on only error issues, nothing related to its logic. I spent about 2 hours and finally almost got the script running OK (it was too late in the afternoon). Here are two notes about the work.

First, I needed to debug PL/SQL procedure codes. We were using SQL*Plus command console. For SQLPlus command codes; I saw that command prompt is used to print out messages, but I this command does not work in a block of procedure codes. Quickly I found the command which can be used for PL/SQL procedure debug. It is actually a very simple one, and I used this one long time ago. Here is my note on this again:

SET SERVEROUTPUT ON
dbms_sql.output.PUT_LINE('message');
...
SET SERVEROUTPUT OFF

The second note is about using VIM to add the about debug command dbms_sql.output.PUT_LINE in to codes. The reason I wanted to use VIM was that a block of similar codes are in a repeatedly pattern (the following codes are mock and simplified ones):

strDDL := 'create synonum ' || '&username' || '.AUsers for ' || '&newUser' || '.AUsers';
nCID := dbms_sql.open_cursor;
dbms_sql.parse(nCID, strDDL, dbms_sql.v7);
nCount := dbms_sql.execute(nCID);
dbms_sql.close_cursor(nCID);


strDDL := 'create synonum ' || '&username' || '.BUsers for ' || '&newUser' || '.BUsers';
nCID := dbms_sql.open_cursor;
dbms_sql.parse(nCID, strDDL, dbms_sql.v7);
nCount := dbms_sql.execute(nCID);
dbms_sql.close_cursor(nCID);

...

I did not want to manually type in the debug commands after the first one in each block. That's too tedious. I decided to use my gVIM (for Windows), using its power to get my codes just in one Find/Replace command. This can be done by grouping feature in Find/Replace command, since I wanted to use partial codes in the first line. I figured out the following VIM command:

:%s\(strDDL :=\)\(.*\);\n\(.*\n.*\n.*\n.*\)/\1\2;\rdbms_output.PUT_LINE(\2);\r\3/g

Notes on above command:

• Replace command: %s/find/replace/g where g is for all.
• In find section, use \(...\) to mark a group. In my above command, there are 3 groups and the 2nd ad 3rd groups will be reused in replacement section.
• The group name used in replace section is reference by \#, such as \2 and \3.
• The line break in find section is \n
• The line break in replace section is \r

This is the result: