以下示例展示了如何处理预定义异常。

delimiter /
CREATE OR REPLACE PACKAGE pkg_ware_mgmt as
    TYPE TYPE_WARE IS RECORD(
         w_id       ware.w_id%TYPE
        ,w_ytd      ware.w_ytd%TYPE
        ,w_tax      ware.w_tax%TYPE 
        ,w_name     ware.w_name%TYPE 
        ,w_street_1 ware.w_street_1%TYPE 
        ,w_street_2 ware.w_street_2%TYPE 
        ,w_city     ware.w_city%TYPE
        ,w_state    ware.w_state%TYPE
        ,w_zip      ware.w_zip%TYPE 
    ); 
    PROCEDURE sp_record_print(p_w_id IN ware.w_id%type);
END;
/
delimiter ;
delimiter /
CREATE OR REPLACE PACKAGE BODY pkg_ware_mgmt 
AS  
     PROCEDURE sp_record_print_by_record(p_record IN ware%ROWTYPE)
      AS
      BEGIN
            dbms_output.put_line('Print a record :');
            dbms_output.put_line('W_ID : ' || p_record.w_id 
                || ', W_YTD : ' || p_record.w_ytd 
                || ', W_TAX : ' || p_record.w_tax 
                || ', W_NAME : ' || p_record.w_name
                || ', W_STREET_1 : ' || p_record.w_street_1
                || ', W_STREET_2 : ' || p_record.w_street_2
                || ', W_CITY : ' || p_record.w_city
                || ', W_STATE : ' || p_record.w_state
                || ', W_ZIP : ' || p_record.w_zip )
            ;
            dbms_output.put_line('');
    END;
    PROCEDURE sp_record_print(p_w_id IN ware.w_id%TYPE )
    IS
        rec_ware    ware%ROWTYPE;   
    BEGIN
        SELECT "W_ID","W_YTD","W_TAX","W_NAME","W_STREET_1","W_STREET_2","W_CITY","W_STATE","W_ZIP"
        INTO rec_ware
        FROM ware 
        WHERE w_id = p_w_id;
        sp_record_print_by_record(rec_ware);    
    EXCEPTION 
        WHEN NO_DATA_FOUND THEN
            dbms_output.put_line('Handle a NO_DATA_FOUND exception [' || sqlcode || ':' || sqlerrm || ']. ');
        WHEN OTHERS THEN
            dbms_output.put_line('Raise an unkown exception !');
            RAISE ;
  END ;
END;
/
delimiter ;
obclient> set serveroutput on;
Query OK, 0 rows affected (0.00 sec)
obclient> call pkg_ware_mgmt.sp_record_print(1);
Query OK, 0 rows affected (0.08 sec)
Print a record :
W_ID : 1, W_YTD : 1200, W_TAX : .1868, W_NAME : W_NAME_1, W_STREET_1 : jTNkXKWXOdh, W_STREET_2 : lf9QXTXXGoF04IZBkCP7, W_CITY : srRq15uvxe5, W_STATE : GQ, W_ZIP : 506811111
obclient> call pkg_ware_mgmt.sp_record_print(3);
Query OK, 0 rows affected (0.00 sec)
Handle a NO-DATA-FOUND exception [-4026:internal error code, arguments: -4026, Nothing to read].
obclient>