Conditional execution in SQLPLUS?
Date: 1995/10/11
Message-ID: <45h54m$fst_at_ixnews5.ix.netcom.com>#1/1
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 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!Received on Wed Oct 11 1995 - 00:00:00 CET