Visit Our UNIX and Linux User Community


sql error code trapping


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting sql error code trapping
# 1  
Old 11-22-2001
sql error code trapping

Hello

#!bin/ksh
sqlplus -s system/manager < |grep '^ORA' |uniq
select * from kk;
set echo on
show spool on
end;
/


EOF

save test.sh

sh test.sh

results

ORA-00942: table or view does not exist

i would like to trap the error in a variable like x and then do echo
$x any ideas how this could be done

regards
Xiamin
# 2  
Old 11-23-2001
Your code on the sqlplus line got zapped. Happens to me too when my posting contains a less-than sign. In the following sample code, I will show less-than-sign as X to avoid getting zapped like yours did.

In solution 1, sqlplus sends its output to both the spooled file and stdout, which is piped into the grep. (When I spool, I prefer to set termout off to suppress stdout, then I follow sqlplus with a grep of the spooled file.) Anyway, we capture the output of this (the ORA- lines) into the variable x.

#!/bin/ksh
x=`sqlplus -s scott/tiger XX EOF | grep '^ORA-'
spool myreport
select * from kk
/
exit
EOF`

echo $x

This could capture multiple lines since many times a single error results in multiple ORA- errors being displayed. If you want to capture just the first word of each line, then add the cut:

#!/bin/ksh
x=`sqlplus -s scott/tiger XX EOF | grep '^ORA-' | cut -d: -f1
select * from kk
/
exit
EOF`

echo error codes: $x

Output from above would be like:

error codes: ORA-00001 ORA-00002

These solutions make use of nesting one unix command within another by using backquotes, such as:

echo "My current directory is `pwd`"

If you are just trying to detect and react to critical errors, your sqlplus code can start with:

whenever sqlerror exit failure

and after exiting sqlplus, check status of $?.
Jimbo
# 3  
Old 11-26-2001
Hello

When i try to excute the script i get the following

sqlplus -s scott/tiger
XX EOF |grep [O]RA | uniq|cut -f1 -d : select Mail creF

my oracle database is up and running .i connect as scott tiger

it is obvious here to me that i am unanle to capture the error code into variable x am i right ?

regards
Hrishy

Last edited by xiamin; 11-26-2001 at 02:03 AM..
# 4  
Old 11-26-2001
I cannot see your entire script, nor the error or results you are getting, so I cannot comment on that. I would suggest running my script as is, and see what you get, then go from there.

But in your last post, I still see my XX. You need to change the XX to two less-than signs "<<".
Jimbo

Previous Thread | Next Thread
Test Your Knowledge in Computers #968
Difficulty: Medium
In 2011. Kernel.org began offering Linux kernel release information in JSON format.
True or False?

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Trapping the error during copy

I have a requirement: During copy command for example: cp -rf <sourceDir> <destinationDir> this command may fails for many reasons like: 1. source or destination directory does not exist 2. destination directory does not have sufficient space 3. directories are not mounted ... Or may... (3 Replies)
Discussion started by: ambarginni
3 Replies

2. Shell Programming and Scripting

Error Trapping

Hi, I have one shell script as below while read SegList do if test -s ${SourceFile_Path}/${Segment_List_Temp} then ls -r -1 ${FTP_Path}/${SegList}.DAT.${Datelist}.GZ|cut -d '.' -f2>>${SourceFile_Path}/${List_Temp} echo "IF above statment Fail I want to Create Emtpy File How to Trapp... (3 Replies)
Discussion started by: samadhanpatil
3 Replies

3. UNIX for Advanced & Expert Users

How to Make Sql Plus Exit with an Error Code

Dear all, How to make sqlplus command to exit with an apt error code in bash script, It always returns 0 for me. Thanks (9 Replies)
Discussion started by: vetrivendhan
9 Replies

4. Shell Programming and Scripting

Trapping program return code

Hello I have a program (prog) that accepts a parameter in order to execute some internal loop grabbing memory in each iteration. I'm using top to monitor the memory usage and to produce an output. Thus I need the program's pid as a parameter to top. I capture pid using myPID=$!. I'm also... (5 Replies)
Discussion started by: pavlosgr
5 Replies

5. Shell Programming and Scripting

curl error trapping in ksh

I hope that I can trap curl errors, and have my shell script error out and quit if curl has any sort of problem. For example, I have the following command in my shell script: curl --trace -n -v --ftp-ssl ftp://xxx.xxx.xxx.xxx:2122 --user user:password -o /tmp/file.txt Works great, except... (2 Replies)
Discussion started by: prestonatwork
2 Replies

6. Shell Programming and Scripting

trapping error for a grep in for a loop

How can I trap and print "cannot find the pattern" when the grep is unable to find the specified pattern in the file using the for loop below ? Any help would be appreciated. bash3.4> cat test_file apple orange pineapple blackberry script: for x in `grep -n "mango" test_file... (4 Replies)
Discussion started by: jville
4 Replies

7. Shell Programming and Scripting

rcp error trapping in rsh

I am writing a program which is something like below: rsh host1 "rcp file dest:directory" I am running this script from a machine host2. host1 has rlogin configuration for host2. but, dest machine has no rlogin configuration for host1 and fails on remote calls. Could anyone tell me how... (2 Replies)
Discussion started by: vvejendla
2 Replies

8. Shell Programming and Scripting

Error Trapping

Hi, Can anybody tell me how to error trap an empty line. If i am asked for a password and I hit enter without entering any text, how do i display an error? Thanks Kev (6 Replies)
Discussion started by: kev112
6 Replies

9. UNIX for Dummies Questions & Answers

ftp error trapping

I have written a UNIX script that will automatically ftp a file to a server. The problem is when I missed enter information w/in the .txt file that contains the userid/password and what file to transfer, I had no way of capturing the failuer of the file transfer. I verified w/in the script that the... (1 Reply)
Discussion started by: dhawkjrscripter
1 Replies

10. UNIX for Dummies Questions & Answers

ftp error trapping

Hi I'm hoping I could get some help on the following. I'm writing a script which will in turn create an ftp script then excecute it. eg echo "user $user $pass" > $script echo "cd $remote_dir" >> $script echo "bi" >> $script echo "mput $file" >> $script echo "bye" >> $script ftp -n -i $ip... (1 Reply)
Discussion started by: Bab00shka
1 Replies

Featured Tech Videos