Unix Oracle

Shell Programming and Scripting
1

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

2

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.

3

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

4

There are many ways to accomplish this.

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:

{
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
5

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

6

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.

7

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

8 
line_no=10
pro=TEST
set -f # <-- Turn globbing off (no filenames)
{
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} >> a.out # <-- Echo error message to a.log
        ;;
        *) echo ${LINE} ;;
    esac
done
echo $?
if [ $? -ne 0 ]; then
    global_proc $line_no $proc_name
else
    echo Success
fi
set +f # <-- Turn globbing back on
9

Many Many thanks for the help and it has worked...

I have one more problem....

Suppose my sql stmt is as follows:-

SELECT a, b, c, d, e, ,f ,g ,h FROM table;
Now I am sending the above select stmt. to a Error Handling shell script.

As Error Handling script will treat this sql statement as positional parameters ..
from $1 - $9. So this will cause problem in executing the above SQL stmt.

So what is the way to deal with it ....

If i use $* in that case it is working...
but along with sql stmt I also want to send the proc_name,Lineno variable then how should i deal with it ..

Thanks
Dhananjay

10

With double quotes; pass each thing with double quotes around them.

11

Excellent ....

Thanks ..IT has worked....
Thanks a lot ....

Thanks & Regards.
Dhananjay

12

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

The echo "WHENEVER SQLERROR EXIT 1 ---- THis line ouputs the error
to the screen....I dont want it ...
Instead I am writing it in a file ...

How should I do it ?

Thanks...

13

Not for me, the only error I encountered was where:

} | while IFS=$(echo '\012\001') read LINE; <===
do

was rejected by ksh88.

Thomas

14

I am not getting u..

15

Maybe this is what you are looking for:

ORA-*|SP2-*) echo "Hido some stuff based on Oracle error" >> yourfile ;;

I prefer to load output to an array (if not too big).

typeset IFS='
'
set -A ARRAY $(your sqlplus command)

Then searching for an error is easy:

(The ORA- and SP2- tests can be combined and made more precise that this example)

if [[ ${ARRAY} = *ORA-* || ${ARRAY} = *SP2-* ]]
then
    for i in ${ARRAY[@]}
    do
        echo $i >> yourerrorlog
    done
fi

This way, all of your error text is dumped to a log rather than the first line.

16

Hi,

From shell script 'A' I am calling shell script 'B' by using ' nohup '.
e.g nohup B.sh

from 'B' I am calling shell scripts such as 'c' 'd' etc...
. C.sh
. D.sh

My question is ....Is it necessary for me to invoke/call scripts 'c' 'd' etc
with ' nohup ' from script 'B'.

Mostly nohup is used for continuining the execution of the scripts ...after
the user logoff ?
I am not sure whether my approach will work or not .

Thanks & Regards,
Dhananjay