UNIX for Advanced & Expert Users

Hi Unix Punters,

Iam calling one PL/SQL script 'a.sql' through a shell script 'a.sh' and capturing
the error message generated during execution of a.sql and storing this message in one log file 'a.log'.

a.sh:-

#!/usr/bin/ksh

RESULT=`sqlplus -s username/password@database <<!
@a.sql
!`
echo $RESULT >> a.log


a.sql:-

Set serveroutput on
WHENEVER SQLERROR EXIT 1
WHENEVER OSERROR EXIT 2

BEGIN
insert into aaa (sal) (select name from bbb);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE ('Errored while inserting to the table aaa');
RAISE;
END;
/
EXIT SUCCESS

The scripts 'a.sh' and 'a.sql' are present in the below location.

/export/home/ITC_rlok>ls -lrt
total 16
drwxrwxrwx 4 ITC_rlok ITC 512 Dec 7 05:09 CR11
drwxrwxrwx 10 ITC_rlok ITC 512 Dec 22 14:21 RMIS
drwxrwxrwx 7 ITC_rlok ITC 512 Feb 6 13:18 TEMP
drwxrwxrwx 5 ITC_rlok ITC 512 Feb 8 08:32 CRED0001
drwxrwxrwx 2 ITC_rlok ITC 512 Mar 13 09:51 TEST
drwxrwxrwx 3 ITC_rlok ITC 512 Mar 27 11:27 SLLD0005
-rwxrwxrwx 1 ITC_rlok ITC 271 Mar 27 12:13 a.sql
-rwxrwxrwx 1 ITC_rlok ITC 90 Mar 27 12:17 a.sh

Now Iam executing the program 'a.sh'.

/export/home/ITC_rlok>a.sh
Errored while inserting to the table aaa BEGIN a.sh a.sql CR11 CRED0001 RMIS SLLD0005 TEMP TEST ERROR at line 1: ORA-01722: invalid number ORA-06512: at line 6

Iam getting the required error message. The problem here is, the error message is also containing other file/directory name which are present along with 'a.sh'.
I dont want to display the file/directory names along with error message. Please help me to avoid this problem.

I need only the error message as per below order:

Errored while inserting to the table aaa
ERROR at line 1: ORA-01722: invalid number
ORA-06512: at line 6

Thanks in advance,
Mysore Ganapati.

Try:

sqlplus -s username/password@database @a.sql >> a.log 2>&1

no luck, result is same

/export/home/ITC_rlok>cat a.log
Errored while inserting to the table aaa BEGIN a.log a.sh a.sql CR11 CRED0001 RMIS SLLD0005 TEMP TEST ERROR at line 1: ORA-01722: invalid number ORA-06512: at line 6

Any other option please?

Try this:

sqlplus -s user/password@database @a.sql 1>>a.log 2>&1

Also, make sure you "zero" your a.log first and try fresh.

Thanks Deal Nodeal,
Below code worked for me.

err_mesg=`sqlplus -S $ORA_USERID_PWD << !
@a.sql
!`
if [ $? -ne 0 ]
then
echo "$CTM_JOBNAME Error " >> $LOGFILE 2>&1
echo "Error message : $err_mesg" >> $LOGFILE 2>&1
exit 1
fi

Thanks to all.

With Regards,
Ganapati.