SQL Error Message

Shell Programming and Scripting
1

Hi,

I have a script that will call a .sql file in unix.
My question is, incase the .sql file encountered an error on execution (e.g. 'ORA-00942: table or view does not exist') is it possible to get that error back to the script that call the .sql file?

Please give me an example.

Thanks.

2

If you want that the execution of your script ends with an error status at the first error encountered, add the following code to your SQL script :

WHENEVER SQLERROR EXIT FAILURE
WHENEVER OSERROR  EXIT FAILURE

Jean-Pierre.

3

This is not a full fledged script but will give you insight how to catch error.

Z=`sqlplus -s user/password@host <<eof
set serveroutput on;
declare
v_message varchar2(1000);
v_value number;
begin
select column into v_value from tablename where rownum<2;
EXCEPTION
WHEN OTHERS THEN
v_message := SQLERRM;
dbms_output.put_line(v_message);
END;
/
EXIT;
eof`
v_message=`echo "$Z" | grep "ORA-"`
echo $v_message

4

Thanks for the reply.

What this code do?

v_message=`echo "$Z" | grep "ORA-"`
echo $v_message

Is it possible to assign value to a unix environment variable in .sql file?

5

v_message=`echo "$Z" | grep "ORA-"`
echo $v_message

Variable Z contains the ouput of sqlplus session.from that output we are searching for "ORA-" string,Which is prefix to oracle error.The lines found with ORA- String are kept in another variable v_message.Second line is printing that oracle error message.