Shell Programming and Scripting

Through a shell script ...

I am invoking sql plus..

Code is as follows...

echo " set feedback off verify off pagesize 0
select column from table " | sqlplus -s/@dbname | read var_col

Now if oracle throws an exception for above select statement ...then how can i catch it ? Please help..asap

Thanks...

I would assume that the sqlplus utility would return with non-zero return code
which you could catch either through $? or by direct shell logic.
However, I would rather rely on something like Perl's DBI (or DBD::Oracle below)
for anything in Oracle/Unix interaction.

You could continue the script with an if statement. For example, if the exception is: except, then you could insert an "if" statement in the script for
handling it:

if [[ $except -ne or -eq for not equal or for equal or Whatever option ]]
then
list of operations to perform
or
else
fi

Mr-synapse

There are many ways to accomplish this.

Code:

echo "WHENEVER SQLERROR EXIT 1
 set feedback off verify off pagesize 0
select column from table " | sqlplus -s/@dbname 
if [[ $? -ne 0 ]] ; then
    do something...
fi

Or you can do something like this:

Code:

{
echo "WHENEVER SQLERROR EXIT 1
set feedback off verify off pagesize 0
select column from table; " | sqlplus -s/@dbname
} | while IFS=$(echo '\012\001') read LINE ; do
    case ${LINE} in
       ORA-*|SP2-*) do some stuff based on Oracle error ;;
       *) echo ${LINE} ;;
    esac
done

Hi ,

can u please interpret from while statement.
I am not getting IFS=$(echo '\012\001') read LINE ?

I tried it but its not giving the proper error msg...

Thanks...

I want to read whole lines back from Oracle queries so I am setting the field separator to newlines only for the WHILE..read loop. Otherwise, the read would read each word rather than the whole line. I use echo '\012' (\012 = newline) because I don't like to break up lines like this IFS='
'. I add \001 because if I use \012 by itself, it doesn't seem to stick for me.

My code is as follows

line_no=10
pro=TEST
{
echo "WHENEVER SQLERROR EXIT 1
set feedback off verify off pagesize 0
select x from tp_mquote where ct1=0; " | sqlplus -s/ /
} | while IFS=$(echo '\012\001') read LINE;
do
case ${LINE} in
ORA-*|SP2-*) echo "Hido some stuff based on Oracle error" ;;
*) echo ${LINE} ;;
esac
done
echo $?
if [ $? -ne 0 ]; then
global_proc $line_no $proc_name
else
echo Success
fi

------Now their is error in sql statement as the column in WHERE clause does not exist in that table...-------------

But before echoing that error it echoes
sql statement and then all FILE NAMES in my pwd
and after that the actual error..

1. How can i avoid those FILE name..
2. I want to write this error to file a.out.

Please help.
Thanks & Regards,
Dhananjay