Thursday, May 30, 2013

Basic error handling for sqlplus

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


=> OS errors

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


=> 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
}


No comments: