Pictures of chickens, flowers, wheat, a boy looking through a magnifying glass, irrigation pipe, soybean pods, and fruits and vegetables.		Cooperative Extension Service
Cooperative Extension Service		Agricultural Experiment Station
Steve Rea's Oracle Tips, Tricks, and Scripts		Search | Publications | Jobs | Personnel Directory | Links

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

This Page was Last Updated on 06/04/07

You Are Visitor Number

© 2006 University of Arkansas Division of Agriculture All rights reserved. Last Date Modified 02/09/2008 Webmaster
	University of Arkansas • Division of Agriculture Cooperative Extension Service 2301 South University Avenue Little Rock, Arkansas 72204 • USA Phone (501) 671-2000 • Fax (501) 671-2209	Mission • Disclaimer • EEO • Privacy • FOI