Re: using conditions in sql*plus

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 - 10:17:03 CDT