Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: using conditions in sql*plus
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') xFrom _______________
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