| Negative value returned from a SQL to a shell script [message #155043] |
Wed, 11 January 2006 21:55  |
Anindya Banerjee
Messages: 5
Registered: December 2004
|
Junior Member |
|
|
Hi gurus,
I am having a peculiar problem.
I have a SQL query (written in a file) to be executed from a shell script. The SQL query is a simple select count(*) query. It looks like:
my_sql.sql
---------------
WHENEVER OSERROR EXIT -2
WHENEVER SQLERROR EXIT -3
select count(*) from TBL1 where <where clause>;
The executing shell script looks like this:
execute_sql.sh
---------------------
sqlplus -s 'user/pass@host' <<EOF
@/home/adm/Temp/my_sql.sql
EOF
status=$?
echo $status
exit $status
Now I have deliberately put the TBL1 as a nonexistent table to check whether it's working for the SQLERROR part. What's happening is in the execute_sql.sh the status is being returned as (256 + (-3)) i.e. 253. For all the negative values it is returning after adding it with 256. But for +ve values it's returning perfectly.
I have to return some negative values as the count(*) could be 0 or more than 0, and only a negative value would trap an OS or SQL error.
It looks more like a shell scripting issue - but please let me know if someone has encountered similar problems and how to get rid of this.
Thanks in Advance,
- Anindya
|
|
|
|
|
|
| Re: Negative value returned from a SQL to a shell script [message #155364 is a reply to message #155186] |
Sun, 15 January 2006 02:27  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
I have shed so much blood trying to make bullet-proof Unix-calling-Oracle scripts. Quite simply, the mechanism for calling SQL*Plus from the OS and capturing any type of error is inadequate. SQL*Plus is an interactive tool, and should stay that way.
For running Oracle scripts from the OS, I now use Perl. Just download DBI and DBD::Oracle modules from the CPAN website, get your SA to install them, and I promise you will never use SQL*Plus in a script again.
_____________
Ross Leishman
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
