Re: Conditional execution in SQLPLUS?
Date: 1995/10/14
Message-ID: <DGFty2.8w0_at_eunet.ch>#1/1
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.chCH 1003 Lausanne - Switzerland Received on Sat Oct 14 1995 - 00:00:00 CET