Re: Conditional execution in SQLPLUS?

chuckh_at_ix.netcom.com (Chuck Hamilton) wrote:

>Is there a way to conditionally execute SQL statements in sqlplus? I'd
>be willing to try PL/SQL too if it would work.
 

>Specifically what I want to do is create a script that'll drop all
>objects for a given user. Needless to say I don't want this script to
>be run accidentally so I want a prompt at the beginning to say
>something like "Are you sure you want to proceed" and give the user a
>chance to bail out. How do I do it.
 

>This is what I've got so far. It's not very elegant. If the user DOES
>press ctrl+c as it says, it kicks them completely out of sqlplus and
>not just out of the script.
 

>PROMPT Are you really sure you want to do this?
>ACCEPT dummy PROMPT 'Press CTRL+C to cancel or ENTER to continue:'
>SET PAGESIZE 0
>SET FEEDBACK OFF
>SPOOL drop_all_objects.sq2
>SELECT 'DROP ' || object_type || ' ' || object_name || ';'
>FROM user_objects
>/
>SPOOL OFF
>SET PAGESIZE 24
>SET FEEDBACK ON
>_at_drop_all_objects.sq2
>--
>Chuck Hamilton
>chuckh_at_ix.netcom.com
 

>Incoming fire has the right of way!

You can add a undefine/define statement and where clause into your sql statement like this

undefine ok
define ok = '&type_in_YES_if_you_are_shure'

SPOOL drop_all_objects.sq2
SELECT 'DROP ' || object_type || ' ' || object_name || ';' FROM user_objects
WHERE '&ok' = 'YES'
/

This will produce the following message at run time :

Enter value for type_in_YES_if_you_ae_shure:

and will only execute drop statements if the answer is YES (in uppercase...)

Hope this help you

Yves Raisin                                     tel   : +41 21 341 81 11
Ofisa Informatique                              fax   : +41 21 341 84 57
Chemin des Charmettes 7                         e-mail: ra_at_ofisa.ch