You Asked

Hi,
How do I return multiple error codes from sqlplus
using statement "whenever sqlerror exit <variable>."
The sqlplus command executes a sql file that contains
a pl/sql block only. In sqlplus I did define a variable
using define command, but I find that I cannot access
this from the pl/sql block. You could define a bind
variable using variable command and set this variable
to store the return code in the pl/sql block, but I
cannot use it in the whenever sqlerror exit statement.
So how do you get around this ?

In short I am trying to do something like this.

---------------------------------------------
#!/usr/bin/ksh

$ORACLE_HOME/bin/sqlplus <<!!

< define a variable s >

WHENEVER SQLERROR EXIT <variable s>

BEGIN
....
....
EXCEPTION
<Here try to set <variable s> to a value
RAISE;
END;

!!
---------------------------------------------

Is something like above pusedo code possible ?

Thanks,
raj.

and Tom said...

So, what you are seeing is this:

tkyte@ORA734.US.ORACLE.COM> variable n number
tkyte@ORA734.US.ORACLE.COM> begin
2 raise program_error;
3 exception
4 when others then
5 :n := 5;
6 raise;
7 end;
8 /
begin
*
ERROR at line 1:
ORA-06501: PL/SQL: program error
ORA-06512: at line 6

tkyte@ORA734.US.ORACLE.COM> print n

N
----------

tkyte@ORA734.US.ORACLE.COM>

and the reason N is still NULL after the block is because the block failed -- hence the output bind variables were never sent OUT of the block to the client (sqlplus). Only if the block succeeds -- will this work. Can something like this work for you:

variable n number

set serveroutput on
begin
raise program_error;
exception
when others then
:n := 5;
dbms_output.put_line( SQLERRM );
end;
/

column n new_val n
select :n n from dual;
exit &n

That has a very similar effect -- we can get the error message (dbms_output will print it for us). We can get the exit code you want and then just exit with it (regardless).

In my testing -- i can get "5" when it fails and when I make it succeed I get 0 back...

Rating

(6 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Any clues on this please

trveor welch, May 28, 2003 - 8:33 am UTC

Tom

A very simple SQL script called from a shell script
which checks the return status.

Trouble is IF NO ROWS FOUND occurs it errors of course
but in my case I can live with that

Any ideas of another approach??
Regards
Trevor

WHENEVER SQLERROR EXIT 255

SPOOL &1

UPDATE F1 SET grp_name = '$UNKNOWN'
WHERE substr(grp_name,1,1) <> '$';

SPOOL OFF
EXIT

May 28, 2003 - 6:35 pm UTC

why does it error? it is never an error to update zero rows.

there will never never ever be "no rows found" thrown by an update (unless a trigger or some side effect throws it).

update t set x = 5 where 1=0;

that will be successful (assuming t exists and all...)

My mistake

A reader, May 28, 2003 - 7:10 pm UTC

Tom,

Thanks for your reply

I'll look at it furthe and let you know
Regards
Trevor

SP2 errors

SC, December 23, 2004 - 3:17 am UTC

Hi Tom.
1.Is there any way to catch SP2 errors the same way sql/os errors are captured?
2. In SQL PLUS guide page 13-154 - is commit going to happen (underlined) after exit when "whenever sqlerror" by default?

Thanks.

December 23, 2004 - 11:22 am UTC

you have whenever and that is about it.

Control propagation in Pro* C

Kuldeep, August 18, 2005 - 6:15 am UTC

Dear Tom,

I have this piece of Pro* C code

void error_handler();

main() {
/* . . . */
EXEC SQL WHENEVER SQLERROR DO error_handler();
/* Query 1 */
EXEC SQL SELECT . . .;
/* Query 2 */
EXEC SQL SELECT . . .;

EXEC SQL WHENEVER SQLERROR GOTO error_label;
/* Query 3 */
EXEC SQL SELECT . . .;
/* Query 4 */
EXEC SQL SELECT . . .;
return;
error_label:
printf(Error Label);
}

void error_handler() {
printf(Error occurred);
return;
}

Sir, here I know if my third query is causing some error my fourth query is not going to be run, because control is skipping that statement and directly jumping to the error_label. But I have some confusion. If my first query is causing some error and the error handler function is going to be invoked then what after that. Where would be the control be. Will it run my second query and other statements after the first query.

Please give some more information on this topic.

Thanks and regards

August 18, 2005 - 4:02 pm UTC

control returns to the place the function error_handler() was called from

(hint: read the generated C code, you'll see that error_handler is just a function call, control returns right there)

unless error_handler does a longjump or exit or something like that, control will return right there.

WHENEVER SQLERROR

Hans, December 29, 2005 - 5:02 pm UTC

I have a table say test(id number(5), col1 varchar2(10), col2 varchar2(10))

I have a sql file with following
WHENEVER SQLERROR EXIT 99
update test
set col1 = 'A'
where id = 1;

update test
set col2 = 'B'
where i = 2 ; ---- DELIBERATELY making a wrong column
name
EXIT

The above .sql file is called from shell script

When I run the above script the first update is successful even the following gives error, It seems Oracle does commit after each statement.

How do i rollback and issue a exit code to be caught in shell script

Regards,
Hans

December 29, 2005 - 5:44 pm UTC

sqlplus commits by default upon exit.  It is not commiting each row (not unless you use the sqlplus command to set autocommit on - then it would)

http://docs.oracle.com/docs/cd/B19306_01/server.102/b14357/ch12052.htm#i2700066


ops$tkyte@ORA10GR2> create table t ( id number, col1 varchar2(10), col2 varchar2(10) );

Table created.

ops$tkyte@ORA10GR2> insert into t values ( 1, 'xxxx', 'xxxx' );

1 row created.

ops$tkyte@ORA10GR2> commit;

Commit complete.

ops$tkyte@ORA10GR2><b>
ops$tkyte@ORA10GR2> whenever sqlerror exit 99 ROLLBACK</b>
ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> update t set col1 = 'a' where id = 1;

1 row updated.

ops$tkyte@ORA10GR2> update t set col1 = 'a' where i = 1;
update t set col1 = 'a' where i = 1
                              *
ERROR at line 1:
ORA-00904: "I": invalid identifier


Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[tkyte@dellpe ~]$ plus

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Dec 29 17:41:04 2005

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

ops$tkyte@ORA10GR2> select * from t;

        ID COL1       COL2
---------- ---------- ----------
         1 xxxx       xxxx

Oracle Guy

A reader, May 18, 2006 - 2:16 pm UTC

Breadcrumb

Question and Answer