Oracle PL/SQL Tutorial/PL SQL Programming/Your Exception

An example of using PRAGMA EXCEPTION_INIT

SQL>
SQL> create table org_level(
  2    company_id number(8)      not null,
  3    org_level varchar2(1) not null
  4  );
Table created.
SQL>
SQL> DECLARE
  2    invalid_org_level EXCEPTION;
  3    PRAGMA EXCEPTION_INIT(invalid_org_level,-2290);
  4  BEGIN
  5    INSERT INTO org_level VALUES (1001,"P");
  6    COMMIT;
  7  EXCEPTION WHEN invalid_org_level THEN
  8    dbms_output.put_line("Organization Level");
  9  END;
 10  /
PL/SQL procedure successfully completed.
SQL>
SQL> drop table org_level;
Table dropped.
SQL>

An example showing handling of pre-defined exceptions

SQL>
SQL> create table product(
  2     product_id number(4)     not null,
  3     product_description varchar2(20) not null
  4  );
Table created.
SQL>
SQL> insert into product values (1,"Java");
1 row created.
SQL> insert into product values (2,"Oracle");
1 row created.
SQL> insert into product values (3,"C#");
1 row created.
SQL> insert into product values (4,"Javascript");
1 row created.
SQL> insert into product values (5,"Python");
1 row created.
SQL>
SQL>
SQL> DECLARE
  2    v_descr VARCHAR2(20);
  3  BEGIN
  4    SELECT product_description
  5    INTO v_descr
  6    FROM product
  7    WHERE product_id =10;
  8    dbms_output.put_line(v_descr);
  9  EXCEPTION WHEN NO_DATA_FOUND THEN
 10    dbms_output.put_line("ERR:Invalid Hierarchy Code 10");
 11  END;
 12  /
ERR:Invalid Hierarchy Code 10
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> drop table product;
Table dropped.

Assign custom exception a number

SQL>
SQL> DECLARE
  2     bad_date EXCEPTION;
  3     PRAGMA EXCEPTION_INIT (bad_date, -1843);
  4  BEGIN
  5     DBMS_OUTPUT.PUT_LINE (TO_DATE ("13/13/99", "MM/DD/YY"));
  6  EXCEPTION
  7    WHEN bad_date
  8    THEN
  9      DBMS_OUTPUT.PUT_LINE("Just twelve months in a year...");
 10  END;
 11  /
Just twelve months in a year...
PL/SQL procedure successfully completed.

Catch "cannot get lock exception"

SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
  2                    ENAME VARCHAR2(10),
  3                    JOB VARCHAR2(9),
  4                    MGR NUMBER(4),
  5                    HIREDATE DATE,
  6                    SAL NUMBER(7, 2),
  7                    COMM NUMBER(7, 2),
  8                    DEPTNO NUMBER(2));
Table created.
SQL>
SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK",    7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7521, "WARD",  "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER",  7839, TO_DATE("2-APR-1981",  "DD-MON-YYYY"), 2975, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10);
1 row created.
SQL>
SQL>
SQL>
SQL> DECLARE
  2    no_lock EXCEPTION;
  3    pragma EXCEPTION_INIT(no_lock, -00054);
  4  BEGIN
  5    FOR my_rec IN (SELECT * from EMP FOR UPDATE NOWAIT)
  6    LOOP
  7      DBMS_OUTPUT.PUT_LINE ("Employee: " || my_rec.ename);
  8    END LOOP;
  9  EXCEPTION
 10    WHEN no_lock THEN
 11      DBMS_OUTPUT.PUT_LINE ("Could not get lock.  Try again later");
 12  END;
 13  /
Employee: SMITH
Employee: ALLEN
Employee: WARD
Employee: JONES
Employee: MARTIN
Employee: BLAKE
Employee: CLARK
Employee: SCOTT
Employee: KING
Employee: TURNER
Employee: ADAMS
Employee: JAMES
Employee: FORD
Employee: MILLER
PL/SQL procedure successfully completed.
SQL>
SQL> drop table emp;
Table dropped.
SQL>

Create your own no_data_found EXCEPTION

SQL>
SQL>
SQL> DECLARE
  2     d VARCHAR2(1);
  3
  4     no_data_found EXCEPTION;
  5
  6  BEGIN
  7
  8     SELECT "dummy" INTO d FROM dual WHERE 1=2;
  9
 10     IF d IS NULL
 11
 12     THEN
 13
 14        RAISE no_data_found;
 15
 16     END IF;
 17
 18  EXCEPTION
 19
 20     WHEN no_data_found
 21
 22     THEN
 23
 24        DBMS_OUTPUT.PUT_LINE ("Trapped the error!?");
 25  END;
 26  /
DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 8

SQL>

Handling user-defined exceptions with a WHEN clause

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> create table org_company_site(
  2     company_id number(8) not null,
  3     site_no number(4) not null
  4  );
Table created.
SQL> insert into org_company_site values (1001,1);
1 row created.
SQL> insert into org_company_site values (1002,2);
1 row created.
SQL> insert into org_company_site values (1003,3);
1 row created.
SQL> insert into org_company_site values (1004,1);
1 row created.
SQL> insert into org_company_site values (1004,2);
1 row created.
SQL> insert into org_company_site values (1004,3);
1 row created.
SQL> insert into org_company_site values (1005,1);
1 row created.
SQL> insert into org_company_site values (1005,4);
1 row created.
SQL> insert into org_company_site values (1005,5);
1 row created.
SQL> insert into org_company_site values (1006,1);
1 row created.
SQL>
SQL>
SQL> BEGIN
  2  insert into company values (3,1007,"O Inc.","O Inc.");
  3  COMMIT;
  4  END;
  5  /
PL/SQL procedure successfully completed.
SQL> DECLARE
  2    sites_undefined_for_org EXCEPTION;
  3    v_cnt NUMBER;
  4  BEGIN
  5    SELECT COUNT(*)
  6    INTO v_cnt
  7    FROM org_company_site
  8    WHERE company_id =1007;
  9    IF (v_cnt=0)THEN
 10      --explicitly raising the user-defined exception
 11      RAISE sites_undefined_for_org;
 12    END IF;
 13  EXCEPTION
 14    --handling the raised user-defined exception
 15    WHEN sites_undefined_for_org THEN
 16      dbms_output.put_line("There are no sites defined for organization 1007");
 17    WHEN OTHERS THEN
 18      dbms_output.put_line("ERR:An error occurred with info :"||
 19      TO_CHAR(SQLCODE)||" "||SQLERRM);
 20  END;
 21  /
There are no sites defined for organization 1007
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> drop table company;
Table dropped.
SQL>
SQL> drop table org_company_site;
Table dropped.
SQL>