Re: Conditional execution in SQLPLUS?

From: Chuck Hamilton <chuckh_at_ix.netcom.com>
Date: 1995/10/19
Message-ID: <4666ij$phi_at_ixnews4.ix.netcom.com>#1/1

roozendaal_at_amc.uva.nl wrote:

>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.......
> end if;
>end;
>/

I gave it a try just as you typed it but it didn't work. The error messages are listed below.

BTW I'm using version 7.1 of ORACLE. Is that the problem?

========================[error messages]=============================
     >SELECT 'DROP ' || object_type || ' ' || object_name || ';'
     *

ERROR at line 6:

ORA-06550: line 6, column 6:
PLS-00103: Encountered the symbol ">" when expecting one of the
following:

begin declare exit for goto if loop mod null pragma raise return select update while <an identifier> etc. Replacing ">" with "begin".

ORA-06550: line 7, column 6:
PLS-00103: Encountered the symbol ">" when expecting one of the
following:

. ( , * _at_ % & - + / mod rem <an identifier> <a double-quoted delimited-identifier> an exponent (**) as etc.
Resuming parse at line 8, column 7.

ORA-06550: line 9, column 4:
PLS-00103: Encountered the symbol "ELSE" when expecting one of the
following:

begin declare end exception exit for goto if loop mod null pragma raise return select update while etc. Resuming parse at line 12, column 1.

ORA-06550: line 13, column 0:
PLS-00103: Encountered the symbol ";" when expecting one of the
following:

begin declare else elsif end exit for goto if loop mod null pragma raise return select update etc.

--
Chuck Hamilton
chuckh_at_ix.netcom.com

Incoming fire has the right of way!

Received on Thu Oct 19 1995 - 00:00:00 CET