Dear all,
How to make sqlplus command to exit with an apt error code in bash script,
It always returns 0 for me.
Thanks
Under what circumstances would you like it to exit with an error (sorry, not sure what you mean by "apt error"!)?
You could, perhaps look at:
SQL> help whenever
WHENEVER OSERROR
----------------
Performs the specified action (exits SQL*Plus by default) if an
operating system error occurs (such as a file writing error).
In iSQL*Plus, performs the specified action (stops the current
script by default) and returns focus to the Workspace if an
operating system error occurs.
WHENEVER OSERROR {EXIT [SUCCESS|FAILURE|n|variable|:BindVariable]
[COMMIT|ROLLBACK] | CONTINUE [COMMIT|ROLLBACK|NONE]}
WHENEVER SQLERROR
-----------------
Performs the specified action (exits SQL*Plus by default) if a
SQL command or PL/SQL block generates an error.
In iSQL*Plus, performs the specified action (stops the current
script by default) and returns focus to the Workspace if a SQL
command or PL/SQL block generates an error.
WHENEVER SQLERROR {EXIT [SUCCESS|FAILURE|WARNING|n|variable|:BindVariable]
[COMMIT|ROLLBACK] | CONTINUE [COMMIT|ROLLBACK|NONE]}
i.e.
$ cat somesql.sh
sqlplus -s scott/tiger << !
whenever sqlerror exit 2;
select 1 from blah;
!
echo Exited with $?
$ ./somesql.sh
select 1 from blah
*
ERROR at line 1:
ORA-00942: table or view does not exist
Exited with 2
1 Like
Let me make it clear
It always returns ZERO for me
Actually i have executing a "sqlplus" command with the params as db username, pwd schema and the pl/sql file[which is having procedure] in -s mode.
In case, if procedure in a file have thrown an exception i need to block some code of execution in a script.
how do i return the exit code post execution of query via sqlplus command.
Suppose i have 3 procedures to be executed, each one are interdependent
proc_1.sql
proc_2.sql
proc_3.sql
if, the exception occured in proc_2.sql then i should not execute proc_3.sql. so i need to have a control over the code based on the exit code after each sqlplus command exectuion.
Thanks every1.
I'm somewhat confused by your use of terminology.
I would consider an "sqlplus" command to be something like "set", "show", etc.
Do the "proc_1.sql", etc. contain SQL, or PL/SQL; DML or DDL?
PL/SQL has exception handling, for example using RAISE , EXCEPTION and WHEN .
Please post one of the "proc_*.sql" scripts.
Sincere apologies for the confusions, am relatively new to this forum and unix 
Proc_*.sql will have pl/sql statements in it. I can able to handle the exception there.
I am doing in all these operations in a single .sh file
Running sqlplus for proc_1 -
then,
sqlplus for proc_2 -
then,
sqlplus for proc_3
Now, the scenario is i need to run the scripts only if the previous procedure ran successfully.
so, if proc_2 is got exception while executing its pl/sql statements then i need to skip the execution of proc_3.
For that i took the value of $? post execution of sqlplus command. But it alwyz returns zero to me irrespective of the exception thrown after the pl/sql execution
Thanks a ton for your patience. 
You still didn't post any PL/SQL
Maybe this will (or won't!) give you some idea.
# Set some stuff up:
$ sqlplus -s scott/tiger
SQL> create table test1( a number(10));
Table created.
SQL> insert into test1 values( 1 ); -- (***) note the value!
1 row created.
SQL> commit;
Commit complete.
create or replace procedure testproc1 is
X number;
begin
select a into X from test1 where a = 1; -- (***)
exception
when no_data_found then
raise;
end;
/
create or replace procedure testproc2 is
X number;
begin
select a into X from test1 where a = 3; -- (xxx)
exception
when no_data_found then
raise;
end;
/
create or replace procedure testproc3 is
X number;
begin
select a into X from test1 where a = 4;
exception
when no_data_found then
raise;
end;
/
^D
$ cat SomeTest
sqlplus -s scott/tiger << !
whenever sqlerror exit 3;
select 'Test 1' from dual;
exec testproc1; -- This works (*1 - it selects where value = 1 (***))
select 'Test 2' from dual;
exec testproc2; -- This fails (*2) (xxx)
select 'Test 3' from dual;
exec testproc3; -- This is never executed (*3)
!
echo Returned $?
$ ./SomeTest
'TEST1
------
Test 1
PL/SQL procedure successfully completed. (*1)
'TEST2
------
Test 2
BEGIN testproc2; END;
*
ERROR at line 1: (*2)
ORA-01403: no data found
ORA-06512: at "SCOTT.TESTPROC2", line 8
ORA-06512: at line 1
(*3) Note that TESTPROC3 is not executed
Returned 3
I think what you really want, is to run sqlplus, send all the output to a log file, then search the log file for known SQLPlus errors.
SQLPlus always returns zero, because the command always works. It may not do what you want, but the unix command does not fail.
ex:
if $STANDARD_OUTPUT_MESSAGES_FILE is my log file:
grep 'ORA-[0-9][0-9][0-9][0-9][0-9]:' ${STANDARD_OUTPUT_MESSAGES_FILE} | egrep -vi "testing|ORA-01034: ORACLE not available|SYS.DBMS_METADATA|OUT bind variable bound to an IN position|unexpected error from call to export_string|ORA-01918|ORA-31684|MGMT_METRICS_RAW|ORA-06512: at line|ORA-01917: user or role |ORA-39082|LibraryCacheNotEmptyOnClose" >>$ORACLE_ERROR_MESSAGES_FILE
grep 'SP2-[0-9][0-9][0-9][0-9][0-9]:' ${STANDARD_OUTPUT_MESSAGES_FILE} >>$ORACLE_ERROR_MESSAGES_FILE
grep 'PLS-[0-9][0-9][0-9][0-9][0-9]:' ${STANDARD_OUTPUT_MESSAGES_FILE} >>$ORACLE_ERROR_MESSAGES_FILE
grep 'UDE-[0-9][0-9][0-9][0-9][0-9]:' ${STANDARD_OUTPUT_MESSAGES_FILE} >>$ORACLE_ERROR_MESSAGES_FILE
grep 'LRM-[0-9][0-9][0-9][0-9][0-9]:' ${STANDARD_OUTPUT_MESSAGES_FILE} >>$ORACLE_ERROR_MESSAGES_FILE
grep 'Job "SYS"."SYS_IMPORT_SCHEMA_[0-9][0-9]" stopped due to fatal error at' ${STANDARD_OUTPUT_MESSAGES_FILE} >>$ORACLE_ERROR_MESSAGES_FILE
grep -i ' not found in ' ${STANDARD_OUTPUT_MESSAGES_FILE} | egrep -vi "ARA_FIL.OPS_PART_TRACKING_LOG" >>$ORACLE_ERROR_MESSAGES_FILE
Plz find the attached shell script and and log files.
You can see that procedure 2 for RGR_1 table has been failed but still the 3rd procedure ran which is not am not intended to execute.
SO i need to run the 3rd one, only if the second procedure ran successfully.
Hope this helps. Thanks again
PS: If i use the below code ,
whenever sqlerror exit 1
whenever oserror exit 2
# save return status
RTNCD=$?
to return the status , It throws an error in script for me...
can i have any update on this post ? ..
Please check if this will be useful to you.
variable exitcode number;
begin
select 23 into :exitcode from dual;
end;
/
exit :exitcode
When running sql above from cli, $? from sqlplus will be 23 in this case.