Re: PL/SQL error handling

• Original Message ---- From: Daniel W. Fink daniel.fink_at_optimaldba.com ... I get output like...

SQL> SELECT test_err(500000,100,102) FROM DUAL;

TEST_ERR(500000,100,102)

ORA-01403: no data found

• I don't want the heading to be returned. Just an error message

>>This is a SQL*Plus feature; the query is valid, so SQL*Plus starts running it; only when you reach a type conversion error (in the first row in this example, but it could be in the 3,003rd row) do you get a runtime error. You can't then tell SQL*Plus to undo the header. You could just
SET HEADING OFF
but then you'd have no headings when things work do properly. Is SQL*Plus part of your application, or are you just using it to show us these examples? Another tool might (for example) select all the rows for a report, and then discard them if any exceptions are raised before end-of-fetch. But that sucks for very large reports...

For a user defined exception I get
SQL> SELECT test_err(58,100,100) FROM DUAL; SELECT test_err(58,100,100) FROM DUAL

       *
ERROR at line 1:
ORA-20101: Invalid snap ids
ORA-06512: at "PERFSTAT.TEST_ERR", line 51

• I don't want the line number in the function. Just an error message

>>Exceptions include the line number. SQL*Plus just prints the exception stack. If you are using a more programmatic environment, you could get the call stack and/or error stack from Oracle (see DBMS_UTILITY.FORMAT_ERROR_STACK and FORMAT_CALL_STACK) and format/output it yourself to remove the line numbers.

There's some very apposite code here: http://www.oracle.com/technology/oramag/oracle/05-mar/o25plsql.html or just google...

Regards Nigel

--
http://www.freelists.org/webpage/oracle-l