| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Conditionally terminating a Batch script.
A copy of this was sent to "Eviloneus" <gshepin_at_webspan.net>
(if that email address didn't require changing)
On Sun, 1 Aug 1999 13:52:26 -0100, you wrote:
>Hi..
>
>I have a nightly batch process that Downloads data into Oracle and
>then I have a batch script that formats/manipulates this data.
>
>My problem is that the first thing my script does is truncate the table
>before
>the data is loaded into it. So if something goes wrong with the load, this
>table
>will remain empty after my script issues the truncate command.
>I don't want the script to continue executing if that is the case because a
>bunch
>of other destructive commands are issued afterwards, with the assumption
>that the data is there.
>
>Is there was a way to test for this???
>something like.
>select count(*) into <VARIABLE> from table_name
>
>if <VARIABLE> = 0 then
> TERMINATE
>else
> CONTINUE
>endif
>
>I know I can do this test with PL_SQL, but unless I'm mistaken I can't issue
>a
>start <script_name>.sql from within PL_SQL. (I tried several times so either
>it is not
>allowed, or I'm not using the correct syntax)
>
>Also, sqlplus does not support (if,then,else) logic (as far as I know).
>
Here is how you can abort a script in sqlplus conditionally. This script does it:
create table t ( x int );
insert into t values ( 1 );
column cnt new_value C
select count(*) cnt from t;
whenever sqlerror exit
begin
if ( &C = 0 ) then
raise_application_error( -20000, 'No Data!' );
end if;
end;
/
whenever sqlerror continue
delete from t;
select count(*) cnt from t;
whenever sqlerror exit
begin
if ( &C = 0 ) then
raise_application_error( -20000, 'No Data!' );
end if;
end;
/
whenever sqlerror continue
prompt We'll never get here!
The colunm cnt new_value causes sqlplus to put the last value selected from any query that references a column called cnt into a macro variable C
We can then test the value of C in plsql and if it is some value we don't like -- raise an error. the "whenever sqlerror exit" clause will cause sqlplus to exit the script at that point.
This script shows the first time the check is made, we continue processing -- if the table is emptied -- we abort processing.
>Can someone give me a hand with this problem
>
>Thanks in advance...
>
--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
Thomas Kyte tkyte_at_us.oracle.com Oracle Service Industries Reston, VA USA
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Sun Aug 01 1999 - 14:56:26 CDT
 |
 |