Shell Programming and Scripting

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

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 $?.

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

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 "<<".