Pseudo-IF Statements In SQL*Plus Scripts

You can trick sqlplus into bypassing sections of your script by generating a begin-comment-block marker (/*) at the point to start the bypass, which acts like a pseudo-IF block, and embedding the end-comment-block marker (*/) in the SQL where the script should continue on from after bypassing that section. In the following, I am bypassing most of my script if the field type variable (fieldtyp) is set to undefined ('U'):

... initial part of script ...
set termout off feedback off heading off
spool bypass.sql
select '/*' from dual where '&fieldtyp' = 'U';
spool off
@bypass.sql
... rest of script ...
-- */
... reset variables and other cleanup ...
set termout on feedback 6 heading on

Note: "heading off", "feedback off", and "--" before "*/" keeps errors from occurring from the bypass script (must be set before the spool commands and after bypass.sql), and "termout off" keeps other messages from being displayed (such as DOC statements; should be reset only after "-- */").

A similar trick also be used to conditionally execute a subscript based on the value tested in the Where portion of the Select, such as if I had wanted to split the above script up into a rest_of_script script file instead of a "rest of script" block:

spool runit.sql
select '@rest_of_script.sql' from dual where '&fieldtyp' <> 'U';
spool off
@runit.sql
... reset variables and other cleanup ...

Copyright © 2009 by Maristream.   All information, scripts, forms, and other material
on this web site are freely available to all Banner and Oracle Database Administrators,
Systems Administrators, Programmers, and others that may need it.

The webmaster who maintains this web site may be reached at srea@maristream.org. Visit our other web sites:

Disclaimer:  As with all software, especially where it affects your vital data, make sure that you examine theses scripts and that you understand what they do before you use them to see if they would have any adverse effect on your particular setup or database layout.  Make a full backup of your database in case you have to revert to your original copy of the database before the scripts were run.  Use these scripts at your own risk.  As a condition of using these scripts, you agree to hold harmless both Maristream and Stephen Rea for any problems that they may cause or other situations that may arise from their use, and that neither Maristream nor I will be held liable for those consequences.