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
*
ERROR at line 1:
ORA-01722: invalid number
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
-- Check to see if the statistic is valid
SELECT DISTINCT statistic#
INTO v_valid_stat
FROM stats$sysstat
WHERE statistic# = p_statistic#;
-- Check to see if the snapshots are valid
IF p_snap1 = p_snap2
THEN
RAISE e_same_snap;
END IF;
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;
END;
/
