Re: PL/SQL error handling
- From: Nigel Thomas <nigel_cl_thomas@xxxxxxxxx>
- To: daniel.fink@xxxxxxxxxxxxxx, oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 5 Apr 2007 00:53:23 -0700 (PDT)
----- Original Message ----
From: Daniel W. Fink daniel.fink@xxxxxxxxxxxxxx
... 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
Other related posts:
