Can't get rc of sqlpus script

Shell Programming and Scripting
1

I have 2 shell scripts. One with data connection like that (script1.sh):

#!/usr/bin/ksh 
query="$1"   
whenever sqlerror exit 3   
connect $user/$pass@sid   
${query} 
EOF 
echo $? 
if [ 0 -ne "$?" ]; 
       then     
        exit 1 
fi

And other is a shell script bigger where I execute sql commands like these:

#!/usr/bin/ksh 
set -x 
$PATH/script1.sh " 
--set serveroutput on 
--set feedback off 
insert into table (column) values ('$1'); 
commit; " 
if [[ $? != 0 ]]   
       then 
             echo "Error" 
             exit 3   
else 
             echo "Ok" 
fi 
............ 
.............

.
The problem is that these second script won't detect error in sql commands and always continues with all code. I put traces and I check that rc is always 0. Could you help me to can detect errors if the sql failed? Thanks!

2

I'm not sure what are you testing ?

Was the insert successfull ?
Execution of the sqlplus ?

sqlplus will return 0 (success) if a insert, for instance, fails due to a constraint.

Check if this example clear things out, you will need to put extra code in sql.

variable exitcode number;
begin
select 23 into :exitcode from dual;
end;
/
exit :exitcode

The $? will be populated with 23 in the shell program running the sqlplus program.

Regards
Peasant.

3

The code of the sql sentence says me:

ERROR at line 1:
ORA-00001: unique constraint (TABLE.PROC_PK) violated

If I put a sql like

select * from not_table_existent;

Also gives me status 0

4

The echo $? loses the sql's exit code and makes the script leave with 0.

5

If you want the exit code displayed and then used in a decision, you might do something like this:-

...
${query}
EOF
RC=$?
echo $RC
if [ $RC -ne 0 ]
then
   exit 1
fi

Does that help?

Robin