Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> PL/SQL error handling
I have a function that I want to return a proper error message (a mix of
standard errors and user defined exceptions) to the calling command.
I would like the error message to be similar to
SQL> SELECT TO_NUMBER('A') FROM DUAL; SELECT TO_NUMBER('A') FROM DUAL
*
Instead, 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/*
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*/
Here is the code snippet (obviously the real function does a lot more...). How do I achieve the terse error format like in the first example?
CREATE OR REPLACE FUNCTION test_err
( p_statistic# IN NUMBER, -- the statistic# p_snap1 IN NUMBER, -- The first snapshot id p_snap2 IN NUMBER -- The second snapshot id ) RETURN NUMBER -- the value of statistic IS v_valid_stat NUMBER := 0; v_min_snap_id NUMBER := 0; v_max_snap_id NUMBER := 0; v_first_snap_id NUMBER := 0; v_last_snap_id NUMBER := 0; v_restart_snap_id NUMBER := 0; v_first_value NUMBER := 0; v_second_value NUMBER := 0; v_last_value NUMBER := 0; v_statistic_delta NUMBER := 0; v_num_restarts NUMBER := 0; v_restarts_left NUMBER := 0; e_same_snap EXCEPTION; e_zero_snap EXCEPTION; e_snap_out_of_range EXCEPTION; e_message VARCHAR2(100);
BEGIN
IF p_snap1 = 0 THEN e_message := 'First snapshot id is 0'; raise_application_error(-20102, e_message); ELSIF p_snap2 = 0 THEN e_message := 'Second snapshot id is 0'; raise_application_error(-20102, e_message); END IF; RETURN v_statistic_delta; EXCEPTION WHEN e_same_snap THEN raise_application_error(-20101, 'Invalid snap ids'); WHEN no_data_found THEN DBMS_OUTPUT.put_line (DBMS_UTILITY.FORMAT_ERROR_STACK); RAISE; WHEN OTHERS THEN DBMS_OUTPUT.put_line (DBMS_UTILITY.FORMAT_ERROR_STACK); RAISE;
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Apr 04 2007 - 23:59:44 CDT