-
Does anyone know how to trap the sqlcode of an sql statement
Code:
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 :-?
-
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
-
-
there is a function called sqlerrm. Have a search coz i don't remember the syntax.
Vinit
-
Hello
I think its working for me .well i am not sure why it is not working on your machine.
regards
Hrishy
-
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
-
whenever sqlerror exit sql.sqlcode
is definitely the key. I use this all the time when communicating back to a ksh.
Jeff Hunter
-
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?
-
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.
Jeff Hunter
-
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]
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|