Re: using conditions in sql*plus

Here is another SQL*Plus possibility:

Rem How To Do Conditional SQL*Plus Rem
Rem Using SQL Creating SQL

Rem *********************************

Set termout off
Set verify off
Set heading off
Set echo off
Set scan off
Column x newline
Spool test.spool

select decode ( count (*)

   ,0,'Prompt Starting _____'
   ,'Prompt _____ Canceled !')
 ,decode ( count (*)
   ,0,''
   ,'exit') x
from _______________
where _______________
;
spool off
set termout on
@test.spool
The SQL to run if the test is true goes here. In this version, SQL*Plus just terminates if the test is false.

Dennis Wetherell wrote:

> PL/SQL is your most flexible option as George suggested, but if all you
> need is stop when wrong version, continue when correct version you could
> use the SQL*Plus WHENEVER SQLERROR command as in the following example.
>
> WHENEVER SQLERROR EXIT ROLLBACK
> declare
> tmp_version varchar2(30);
> begin
> select client_version into tmp_version from version_table where
> client_version =
> required_version;
> end;
> /
>
> if the where clause fails, SQL*Plus terminates immediately, if it doesn't,
> processing continues. Drawback to this is that if an error encountered
> during execution of remainder of code, execution will terminate imediately
> at that point.
>
> Check out your SQL*Plus manual for full documentation and other parameters
> to the WHENEVER command;
>
> Dennis Wetherell
> dwether_at_mail.arco.com
>
> George Tsiamitas <gtsiam_at_link-systems.gr> wrote in article
> <01bd9868$846e8c20$866542c3_at_GT.link-systems.gr>...
> > SQL*Plus is poor for such things. Use PL/SQL. (it could be made with
> > SQL*Plus though with multiple effort)
> >
> > Jan Timmermans <jtimmerm_at_luc.ac.be> wrote in article
> > <6d6jlc$c1c$1_at_dalet.belnet.be>...
> > > Hi,
> > >
> > > to make changes in the definition of excisting tables, views,
> procedures
> > and
> > > triggers we send sql scripts to our customers.
> > > These sql scripts can be executed from within sql*plus.
> > > The version of the tables is being stored in a special table VERSION.
> > > When an update script is executed we want to check whether the version
> > > of the tables is correct for that script. If not, the customer has to
> be
> > > notified
> > > and the script stops, if so the scripts continous normally.
> > > Problem : I can't find a way in sql*plus to excecute different code
> > > depending
> > > on a condition ( if then else ? ) or to stop an sql*script.
> > > Can anybody help me please !
> > >
> > > Thanx
> > >
> > > Jan
> > >
> > >
> > >
> >
Received on Tue Jun 16 1998 - 17:51:26 CDT