tmsmeq
Last Updated: February 25, 2016
·
2.1K
· victorbrca
409302 10151131950910230 214813917 n 2

Basic error handling for sqlplus

=> Handling Command errors

The option WHENEVER SQLERROR will provide an error should the output of a sqlplus command be an error. If you mistype a command (like using 'scelect' instead of 'select'), it will not be caught here, rather it will provide an 'SP2-####' error.

WHENEVER SQLERROR EXIT [{error code}|SQL.ERROR]

example

WHENEVER SQLERROR EXIT 1

=> Handling OS errors

WENEVER OSERROR EXIT [{error code}|SQL.ERROR]

=> Handling Most Errors

Using a PL/SQL block will also help catching most errors

runSelect () {
command sqlplus -S [user]/[passwd]@[sid] > /dev/null 2>&1 <
SET FEEDBACK OFF
SET LINESIZE 200
SET SERVEROUTPUT ON FORMAT WRAP
WHENEVER SQLERROR EXIT 1;
spool /tmp/[file].out

DECLARE
  cursor c1 is
  select [*|{column}] str
    from [table];

BEGIN
  for c1_rec in c1
    loop
      dbms_output.put_line(c1_rec.str);
    end loop;
END;
/
spool off

EOF
}
Say Thanks
Respond
Filed Under