Visit Our UNIX and Linux User Community


SQLPLUS Error Capturing


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting SQLPLUS Error Capturing
# 1  
Old 06-07-2011
SQLPLUS Error Capturing

This is my .sh file
Code:
sqlplus -s my_user/my_pswd@mydb  @my_sql.sql

ret_code=$?

if [ $ret_code -ne 0 ]
then

echo "return code is $ret_code "
echo "Failed"
else
echo "return code is $ret_code "
echo "Success"
fi

=====================================================

and my_sql.sql is
Code:
whenever SQLERROR exit SQL.SQLCODE;

select 1/0 from dual;

quit;

=======================================================

WHen I run this, this is the output I'm getting,
Code:
select 1/0 from dual
        *
ERROR at line 1:
ORA-01476: divisor is equal to zero
 
 
return code  is 196
Failed.

==================================
My question is, why the error code is 196 instead of 1476 ?

Last edited by Franklin52; 06-08-2011 at 04:53 AM.. Reason: Please use code tags
# 2  
Old 06-07-2011
error code 196 is from the unix and not from oracle.
when you do $? you are getting the last executed unix command status.
# 3  
Old 06-07-2011
SQLPLUS Error Capturing

Ok, I hope the last executed, before I echo the return code,unix command is sqlplus.

What does it imply if the return code of sqlplus command is 196? ..
is there a way I can print -1476 to print the actual error?

Thanks,
Safath.
# 4  
Old 06-07-2011
because 196 is the lower eight bits of the number 1476 (integer);

number returned in $? can be 0 - 255 - the lowest 8 bits of a 4 byte number.
# 5  
Old 06-07-2011
I'm sorry, How to calculate the lowest 8 bits of any given number? Any pointers on this? Thanks in advance....

Previous Thread | Next Thread
Test Your Knowledge in Computers #21
Difficulty: Easy
James Gosling developed the Java Programming Language.
True or False?

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Capturing error codes in SFTP commands

Hi, I have been using the SFTP commands in my reusable shell scripts to perform Get/Put operation. The script has a list of 6 errors which i am capturing through the log file using grep command. But I feel there might me more errors which the script might need to capture. I tried to capture the... (2 Replies)
Discussion started by: Bobby_2000
2 Replies

2. Shell Programming and Scripting

Sqlplus error - sqlplus -s <login/password@dbname> : No such file or directory

i am using bash shell Whenever i declare an array, and then using sqlplus, i am getting sqlplus error and return code 127. IFS="," declare -a Arr=($Variable1); SQLPLUS=sqlplus -s "${DBUSER}"/"${DBPASS}"@"${DBASE} echo "set head off ; " > ${SQLCMD} echo "set PAGESIZE 0 ;" >> ${SQLCMD}... (6 Replies)
Discussion started by: arghadeep adity
6 Replies

3. Shell Programming and Scripting

Capturing Oracle SQL Error Codes

My issue pertains to earlier posts like exit unix script after sqlerror in procedure - dBforums SQLPLUS Error Capturing | Unix Linux Forums | Shell Programming and Scripting We are executing PL/SQL blocks from shell scripts using SQLPLUS. The error code has to be captured into a logfile that... (3 Replies)
Discussion started by: jerome_rajan
3 Replies

4. Shell Programming and Scripting

Capturing the invalid records to error file

HI, I have a source file which has the below data. Tableid,table.txt sourceid,1,2,3,4,5,6 targetid,1,2,3,4,5,6 Tableid,table sourceid,1,2,3,4,5,6 targetid,1,2,3,4,5,6 Tableid,table.txt sourceid,1,2,3,4,5,6 targetid,1,2,3,4,5,6 Tableid,table sourceid,1,2,3,4,5,6 targetid,1,2,3,4,5,6... (6 Replies)
Discussion started by: shruthidwh
6 Replies

5. Shell Programming and Scripting

sqlplus error output to different error log file

HELLO, I am using such a command to write oracle sqlplus query result to text file: sqlplus -S xxx/xxx@xxxxxxx @\tmp\2.sql>\tmp\123.txt Is it possible to script that: If command succesfull write in \tmp\log.txt: timestamp and "succeded" and create 123.txt with results else If error... (2 Replies)
Discussion started by: tomasba
2 Replies

6. Shell Programming and Scripting

Any suggestion on Capturing SFTP error in log ???

Hello All, I have searched the thread for capturing and got some suggestion but that is not working for me. I am trying to sftp to a window server from unix server and getting file there with out any issue .But i wan't to capture the error --in case a file is not exist in remote. Example :... (1 Reply)
Discussion started by: jambesh
1 Replies

7. Shell Programming and Scripting

SQLPLUS error

I am running a script that invokes SQLPLUS. During the execution I get the following: SQL*Plus: Release 9.2.0.8.0 - Production on Mon Jun 11 16:12:50 2007 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to: Oracle9i Enterprise Edition Release 9.2.0.8.0 -... (3 Replies)
Discussion started by: ssmith001
3 Replies

8. UNIX for Advanced & Expert Users

Capturing failed FTP error

Hi All, Please check the below ftp related job, which is deleting the files from remote host. Problem is it is not capturing the ftp failure error, and the exit status is still '0' eventhough the deletable files are not present in remote location OR ftp credential are incorrect. ... (5 Replies)
Discussion started by: ganapati
5 Replies

9. UNIX for Advanced & Expert Users

Capturing Oracle Shutdown error

Hi, Iam calling 3 sql scripts through one shell script 'rmia.sh'. Till now this was working fine, but last time while calling the SQL scripts, Oracle was down. But amazingly the exit status was '0' (success)!!! Below is the shell code: #!/usr/bin/ksh -x assign_file asql a.sql 1... (15 Replies)
Discussion started by: ganapati
15 Replies

10. UNIX for Dummies Questions & Answers

error connecting to sqlplus

Hi, I wrote a shell script to call oracle procedure. But when i am trying to connet sqlplus with the fallowing statement It is giving me error " callproce.sh : sqlplus: not found". What could be the problem. sqlplus -s $CONNECT_STRING >$LOGFILE <<!! thank u all papachi (2 Replies)
Discussion started by: papachi
2 Replies

Featured Tech Videos