PL/SQL error handling

From: Daniel W. Fink <daniel.fink_at_optimaldba.com>
Date: Wed, 04 Apr 2007 22:59:44 -0600
Message-ID: <46148240.6020502@optimaldba.com>

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;
    /

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

Received on Wed Apr 04 2007 - 23:59:44 CDT