Pseudo-CASE Statements In SQL*Plus Scripts

Enlarging on the Pseudo-IF code, you can also do a "case"-like block by embedding several Select SQL generators in the Spool block, with each one uniquely identified in the Where portion of the Select.  The snippet below generates an Accept command based on the field type entered earlier (fieldtyp 'C' for character or 'N' for number; note the pairs of single quotes embedded in the string to generate, such as after 'prompt', for which the Select statement generates a single quote, instead of seeing a non-paired single quote as the string terminator):

spool case.sql
select 'set termout on verify on' from dual;
select 'accept fieldstr char prompt ''Enter string to find (like Myvalue): '''
     from dual where '&fieldtyp' = 'C';
select 'accept fieldnum num prompt ''Enter number to find (like 15.00): '''
     from dual where '&fieldtyp' = 'N';
select 'set termout off verify off' from dual;
spool off
@case.sql

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.