Re: using conditions in sql*plus

From: John P. Higgins <jh33378nospam_at_deere.com>
Date: 1998/02/27
Message-ID: <34F74EAB.1D65@deere.com>#1/1

Try this:

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.

In this sample, I was testing the existence of a row in a table. If the row did not exist (count = 0), I wanted to run some more SQL statements.
If the row did not exist (count = anything else), I just wanted to quit SQL*Plus.

So the query generates one or the line into test.spool. The lines will contain one of two SQL*Plus scripts: Prompt Starting _________
or
Prompt __________ Canceled
exit

Then I have SQL*Plus execute the script it just generated. If the test failed, I get the ________ Canceled message and SQL*Plus quits. Otherwise I get the Starting __________ message and SQL*Plus continues with my SQL script.

You can modify this sample many, many ways.

Hope this helps.
Jan Timmermans wrote:
>
> 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 Fri Feb 27 1998 - 00:00:00 CST