Only Toad can debug SQL Scripts

Bert Scalzo — Wed, 16 Jul 2008 11:45:00 GMT

I have been doing Oracle work for just over two decades – and I’ve probably written thousands of SQL scripts. Many of those were DBA oriented in their task nature, but even application development presents times where SQL scripts are used. For example, the need may arise for the operating systems’ or third party schedulers to invoke a shell script to execute a SQL script to execute some PL/SQL code.

Historically while developing such scripts, there were no tools for debugging them – other than printing with DBMS_OUTPUT. SQL*Plus back then, nor SQL Developer now, offers the same debugging capability for SQL scripts that PL/SQL programmers have become accustomed. But years ago Toad rectified that situation – yet not too many people know about or leverage this one-of-a-kind functionality.

Let use my Dynamic SQL script from last week’s blog as an example.

-- ri_off.sql
set pagesize 0
set feedback off
set term off
spool c:\temp\ri_off.tmp
select 'alter table '||owner||'.'||table_name||' disable constraint 
'||constraint_name||';'
 from user_constraints
 where constraint_type = 'R'
 and status = 'ENABLED';
spool off
set term on
set feedback on
set echo on
@c:\temp\ri_off.tmp

When first developing this script, how can I verify what’s going on inside – before actually doing the last step and running its output. Of course the obvious answer is to comment out the very last line and then visually inspect the temporary file’s generated code for correctness. But what if I needed to peak under the covers and see both how and why the results came out the way they did. That’s where Toad’s script debugger comes into play.

First I load the script into the editor and turn on the SQL script debugger as shown here.

After that you’ll see how the editor’s toolbar icons for debugging are now enabled, as highlighted here.

Now we can step through our SQL script code just like we can in Toad’s PL/SQL debugger. First let’s update the sample code so I can show something of value. I’ve added two lines as shown here so that I have something to examine in the debugger (namely, I want to see the value of the var_xxx variable.

-- ri_off.sql
set pagesize 0
set feedback off
set term off
COLUMN xxx NOPRINT new_value var_xxx;
SELECT count(*) as xxx from all_users;
spool c:\temp\ri_off.tmp
select 'alter table '||owner||'.'||table_name||' disable constraint 
'||constraint_name||';'
 from user_constraints
 where constraint_type = 'R'
 and status = 'ENABLED';
spool off
set term on
set feedback on
set echo on
--@c:\temp\ri_off.tmp

So now I can set a break on line 8, run the script to that point, and examine my variables as shown here. Only Toad offers this unique capability – and it’s too darn useful not to take advantage of J

Bert Scalzo's Blog

Only Toad can debug SQL Scripts