RE: Conditional execution in SQLPLUS?

In Article <45h54m$fst_at_ixnews5.ix.netcom.com> chuckh_at_ix.netcom.com (Chuck Hamilton) writes:
>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:'
 accept yes_no prompt 'Are you sure, type "YES" '
>SET PAGESIZE 0
>SET FEEDBACK OFF
>SPOOL drop_all_objects.sq2

declare

   yes_i_do varchar2(3);
begin

   yes_i_do := &&yes_no;
   if yes_i_do = 'YES' then

     >SELECT 'DROP ' || object_type || ' ' || object_name || ';'
     >FROM user_objects
     >;
   else
     -- do nothing.......

hope this helps..........
Jos Received on Thu Oct 12 1995 - 00:00:00 CET