A complete example using RAISE_APPLICATION_ERROR : raise_application_error « PL SQL Programming « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. PL SQL Programming
3. raise_application_error

SQL>
SQL>
SQL> create table company(
  2     product_id        number(4)    not null,
  3     company_id          NUMBER(8)    not null,
  4     company_short_name  varchar2(30) not null,
  5     company_long_name   varchar2(60)
  6  );

Table created.

SQL> insert into company values(1,1001,'A Inc.','Long Name A Inc.');

1 row created.

SQL> insert into company values(1,1002,'B Inc.','Long Name B Inc.');

1 row created.

SQL> insert into company values(1,1003,'C Inc.','Long Name C Inc.');

1 row created.

SQL> insert into company values(2,1004,'D Inc.','Long Name D Inc.');

1 row created.

SQL> insert into company values(2,1005,'E Inc.','Long Name E Inc.');

1 row created.

SQL> insert into company values(2,1006,'F Inc.','Long Name F Inc.');

1 row created.

SQL>
SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE org_proc
  2                  (p_flag_in VARCHAR2,
  3                   p_product_id NUMBER,
  4                   p_company_id NUMBER,
  5                   p_company_short_name VARCHAR2,
  6                   p_company_long_name VARCHAR2)
  7  IS
  8    v_error_code NUMBER;
  9  BEGIN
 10    IF (p_flag_in ='I')THEN
 11      BEGIN
 12        INSERT INTO company VALUES(p_product_id,p_company_id,p_company_short_name,p_company_long_name);
 13      EXCEPTION WHEN OTHERS THEN
 14        v_error_code :=SQLCODE;
 15        IF v_error_code =-1 THEN
 16          RAISE_APPLICATION_ERROR(-20000,'Organization '||TO_CHAR(p_company_id)||' already exists.');
 17        ELSIF v_error_code =-2291 THEN
 18          RAISE_APPLICATION_ERROR(-20001,'Invalid Hierarchy Code '||TO_CHAR(p_product_id)||' specified.');
 19        END IF;
 20      END;
 21    ELSIF (p_flag_in ='C')THEN
 22      BEGIN
 23        UPDATE company
 24        set company_short_name =p_company_short_name,
 25        company_long_name =p_company_long_name
 26        WHERE product_id =p_product_id
 27        AND company_id =p_company_id;
 28        IF SQL%NOTFOUND THEN
 29          RAISE_APPLICATION_ERROR(-20002,'Organization '||TO_CHAR(p_company_id)||' does not exist.');
 30        END IF;
 31      END;
 32    ELSIF (p_flag_in ='D')THEN
 33      BEGIN
 34        DELETE company
 35        WHERE product_id =p_product_id
 36        AND company_id =p_company_id;
 37        IF SQL%NOTFOUND THEN
 38          RAISE_APPLICATION_ERROR(-20003,'Organization '||TO_CHAR(p_company_id)||' does not exist.');
 39        END IF;
 40      EXCEPTION WHEN OTHERS THEN
 41        v_error_code :=SQLCODE;
 42        IF v_error_code =-2292 THEN
 43          RAISE_APPLICATION_ERROR(-20004,'Organization '||TO_CHAR(p_company_id)||' site details defined for it.');
 44        END IF;
 45      END;
 46    END IF;
 47  END;
 48  /

Procedure created.

SQL>
SQL> DECLARE
  2    v_product_id NUMBER := 6;
  3    v_company_id NUMBER := 1010;
  4    v_company_short_name VARCHAR2(30):= 'Office Inc.';
  5    v_company_long_name VARCHAR2(60):= 'Office Inc.';
  6    excep1 EXCEPTION;
  7    PRAGMA EXCEPTION_INIT(excep1,-20000);
  8    excep2 EXCEPTION;
  9    PRAGMA EXCEPTION_INIT(excep2,-20001);
 10  BEGIN
 11    org_proc('I',v_product_id,v_company_id,v_company_short_name,v_company_long_name);
 12  EXCEPTION
 13    WHEN excep1 or excep2 THEN
 14      DBMS_OUTPUT.PUT_LINE(SQLERRM);
 15    WHEN OTHERS THEN
 16      DBMS_OUTPUT.PUT_LINE(TO_CHAR(SQLCODE)||SQLERRM);
 17  END;
 18  /

PL/SQL procedure successfully completed.

SQL>
SQL> drop table company;

Table dropped.

SQL>
SQL>