trap sqlerr in sql*plus exit

[pando]

Does anyone know how to trap the sqlcode of an sql statement

sqlplus user/passwd@move << EOF
select * from kk;
exit sql.sqlcode;
EOF
echo $?

This works partially, the code returned should be ORA-942 but it returns 174 because in UNIX the exit status is from 0 - 255, anyone know a way to overcome this :-?

[hrishy]

Hello

You might try this

#!/bin/bash
sqlplus user/passwd@move << EOF
select * from kk;
variable temp number
whenever sqlerror exit sql.sqlcode;
begin
:temp := 25; -- this is really a function call, not 25
if ( :temp <> 0 )
then
raise_application_error( (-20000-224) - :temp, 'test' );
end if;
end;
/
EOF
echo $?

i havent tested this as i am not very good at shell scripts

the crux of the logic is raise_application_error( (-20000-224) - :temp, 'test' );

We can raise errors in a given range but the probelm lies with unix shell as the shell will only keep an unsigned byte in the status return value (values 0..255). It takes our exit file and just looks at that last byte. By using -20000-224 and subtracting from the your return code -- we end up exiting with the value of your return code

regards
Hrishy

[pando]

that doesnt work =(

[vinit]

there is a function called sqlerrm. Have a search coz i don't remember the syntax.

[hrishy]

Hello

I think its working for me .well i am not sure why it is not working on your machine.

regards
Hrishy

[amar]

Here's a quick and dirty solution

Put the following (sql commands) in a shell script

#!/bin/sh
# tt
sqlplus -s uid/pwd@svc < select * from kk;
EOF
# end tt

at the shell prompt try the following:

$ chmod 755 tt
$ tt | grep ORA | cut -c 5-9

-amar

[marist89]

whenever sqlerror exit sql.sqlcode

is definitely the key. I use this all the time when communicating back to a ksh.

[pando]

Hi marist, that works but the code it returns is incorrect, if you get ORA-0942 it will return $? = 174 because in UNIX only 0 - 255 is used

Amar I understand it´s easier use a file and treat the file, I thought there is another way of doing it

Vinit sqlerrm works in PL/SQL but not SQL

hrishy what is the $? you get?

[marist89]

Ah, yes. I didn't fully read the question. I usually only care if the return code is 0 or not.

I don't think there is any way you will be able to return a status > 255. You can return the exit status in an environment variable and exit with a non-zero value. When you exit with the non-zero value you can then check the special environment variable.

[hrishy]

Hello Pando

That was a typing mistake from me i am extremely sorry.here is teh right solution .


#!/bin/bash
sqlplus -s user/passwd@move << EOF |grep '^ORA'
select * from kk;
set echo on
variable temp number
whenever sqlerror exit sql.sqlcode;
begin
:temp := 25; -- this is really a function call, not 25
if ( :temp <> 0 )
then
raise_application_error( (-20000-224) - :temp, 'test' );
end if;
end;
/
EOF
set x=$?
echo $?

now save this file as test.sh

and now do

sh test.sh

output would be
ORA-00942: table or view does not exist
ERROR at line 1:
ORA-20279: test
ORA-06512: at line 5


regards
Hrishy




[Edited by hrishy on 11-22-2001 at 01:45 AM]