Shell Programming and Scripting

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.

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 :

Code:

WHENEVER SQLERROR EXIT FAILURE
WHENEVER OSERROR  EXIT FAILURE

Jean-Pierre.

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

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?

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.