Propogating a Server-side Customized Error Number and Error Message to client program using PRAGMA EXCEPTION_INIT : Raise Exception « PL SQL Programming « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. PL SQL Programming
3. Raise Exception

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 '||
 17          TO_CHAR(p_company_id)||' already exists.');
 18        ELSIF v_error_code =-2291 THEN
 19          RAISE_APPLICATION_ERROR(-20001,'Invalid Hierarchy Code '||
 20          TO_CHAR(p_product_id)||' specified. Cannot create organization.');
 21        END IF;
 22      END;
 23    ELSIF (p_flag_in ='C')THEN
 24      BEGIN
 25        UPDATE company
 26        set company_short_name =p_company_short_name,
 27        company_long_name =p_company_long_name
 28        WHERE product_id =p_product_id
 29        AND company_id =p_company_id;
 30        IF SQL%NOTFOUND THEN
 31          RAISE_APPLICATION_ERROR(-20002,'Organization '||
 32          TO_CHAR(p_company_id)||
 33          ' does not exist.');
 34        END IF;
 35      END;
 36    ELSIF (p_flag_in ='D')THEN
 37      BEGIN
 38        DELETE company
 39        WHERE product_id =p_product_id
 40        AND company_id =p_company_id;
 41        IF SQL%NOTFOUND THEN
 42          RAISE_APPLICATION_ERROR(-20003,'Organization '||
 43          TO_CHAR(p_company_id)||
 44          ' does not exist. Cannot delete info for the same.');
 45        END IF;
 46      EXCEPTION WHEN OTHERS THEN
 47        v_error_code :=SQLCODE;
 48        IF v_error_code =-2292 THEN
 49          RAISE_APPLICATION_ERROR(-20004,'Organization '||
 50          TO_CHAR(p_company_id)||
 51          ' site details defined for it.');
 52        END IF;
 53      END;
 54    END IF;
 55  END;
 56  /

Procedure created.

SQL>
SQL> DECLARE
  2    v_product_id NUMBER := 6;
  3    v_company_id NUMBER := 1010;
  4    v_company_short_name VARCHAR2(30):= 'O Inc.';
  5    v_company_long_name VARCHAR2(60):= 'O 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>