Oracle PL/SQL/PL SQL/Handle Exception

A procedure that uses a WHEN OTHERS clause

   
SQL> -- Here is an example of a procedure that uses a WHEN OTHERS clause:
SQL>
SQL> -- create demo table
SQL> create table Employee(
  2    ID                 VARCHAR2(4 BYTE)         NOT NULL,
  3    First_Name         VARCHAR2(10 BYTE),
  4    Last_Name          VARCHAR2(10 BYTE),
  5    Start_Date         DATE,
  6    End_Date           DATE,
  7    Salary             Number(8,2),
  8    City               VARCHAR2(10 BYTE),
  9    Description        VARCHAR2(15 BYTE)
 10  )
 11  /
Table created.
SQL>
SQL> -- prepare data
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2               values ("01","Jason",    "Martin",  to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto",  "Programmer")
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values("02","Alison",   "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values("03","James",    "Smith",   to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values("04","Celia",    "Rice",    to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values("05","Robert",   "Black",   to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)
  2                values("06","Linda",    "Green",   to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York",  "Tester")
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)
  2                values("07","David",    "Larry",   to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York",  "Manager")
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)
  2                values("08","James",    "Cat",     to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
  3  /
1 row created.
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
  2  /
ID   FIRST_NAME LAST_NAME  START_DAT END_DATE      SALARY CITY       DESCRIPTION
---- ---------- ---------- --------- --------- ---------- ---------- ---------------
01   Jason      Martin     25-JUL-96 25-JUL-06    1234.56 Toronto    Programmer
02   Alison     Mathews    21-MAR-76 21-FEB-86    6661.78 Vancouver  Tester
03   James      Smith      12-DEC-78 15-MAR-90    6544.78 Vancouver  Tester
04   Celia      Rice       24-OCT-82 21-APR-99    2344.78 Vancouver  Manager
05   Robert     Black      15-JAN-84 08-AUG-98    2334.78 Vancouver  Tester
06   Linda      Green      30-JUL-87 04-JAN-96    4322.78 New York   Tester
07   David      Larry      31-DEC-90 12-FEB-98    7897.78 New York   Manager
08   James      Cat        17-SEP-96 15-APR-02    1232.78 Vancouver  Tester
8 rows selected.
SQL>
SQL>     CREATE OR REPLACE PROCEDURE add_new_employee
  2          (employee_id_in IN NUMBER, salary_in IN NUMBER)
  3      IS
  4          no_salary EXCEPTION;
  5
  6      BEGIN
  7          IF salary_in = 0 THEN
  8              RAISE no_salary;
  9          ELSE
 10              INSERT INTO employee (id, salary ) VALUES ( employee_id_in, salary_in );
 11      END IF;
 12
 13      EXCEPTION
 14           WHEN no_salary THEN
 15              raise_application_error (-20001,"You must have salary in order to insert the employee.");
 16
 17           WHEN OTHERS THEN
 18              raise_application_error (-20002,"When other.");
 19
 20      END;
 21      /
Procedure created.
SQL>
SQL> exec add_new_employee("99",0);
BEGIN add_new_employee("99",0); END;
*
ERROR at line 1:
ORA-20001: You must have salary in order to insert the employee.
ORA-06512: at "sqle.ADD_NEW_EMPLOYEE", line 15
ORA-06512: at line 1

SQL>
SQL> -- clean the table
SQL> drop table Employee
  2  /
Table dropped.
SQL>
SQL>
SQL>

Check and output sqlcode sqlerrm

    
SQL>
SQL>
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>  declare
  2      l_emp emp%rowtype;
  3    begin
  4      select *
  5        into l_emp
  6        from emp;
  7       dbms_output.put_line("EMPNO: " || l_emp.empno);
  8      dbms_output.put_line("ENAME: " || l_emp.ename);
  9    exception
 10      when others then
 11        dbms_output.put("Exception encountered! (");
 12        dbms_output.put_line(sqlcode || "): " || sqlerrm);
 13        raise;
 14    end;
 15    /
Exception encountered! (-1422): ORA-01422: exact fetch returns more than requested number of rows
 declare
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 13

SQL>
SQL> drop table emp;
Table dropped.

Generic error handling to handle any type of error

    
SQL>
SQL> declare
  2      str  varchar2(50);
  3  begin
  4      str := "CAT";
  5  exception
  6      when others then
  7          raise_application_error (-20100, "error#" || sqlcode || " desc: " || sqlerrm);
  8  end;
  9  /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>

noroom exception

    
SQL> CREATE TABLE myHotel(
  2     room_id INTEGER,
  3     resident_count INTEGER,
  4     room_capacity INTEGER,
  5     name VARCHAR2(20)
  6  );
Table created.
SQL>
SQL> INSERT INTO myHotel(room_id, resident_count, room_capacity, name) VALUES(101, 20, 20, "First Room");
1 row created.
SQL> INSERT INTO myHotel(room_id, resident_count, room_capacity, name) VALUES(102, 19, 20, "Second Room");
1 row created.
SQL> INSERT INTO myHotel(room_id, resident_count, room_capacity, name) VALUES(103, 10, 20, "Third Room");
1 row created.
SQL> INSERT INTO myHotel(room_id, resident_count, room_capacity, name) VALUES(104, 0, 20, "Fourth Room");
1 row created.

SQL>
SQL> CREATE OR REPLACE PROCEDURE addstudent (roomin IN INTEGER)
  2  IS
  3     roomname       VARCHAR2 (20);
  4     residentcount   PLS_INTEGER;
  5     capacity       PLS_INTEGER;
  6     noroom         EXCEPTION;
  7  BEGIN
  8     SELECT resident_count, room_capacity, name
  9       INTO residentcount, capacity, roomname
 10       FROM myHotel
 11      WHERE room_id = roomin;
 12
 13     IF residentcount > capacity - 1
 14     THEN
 15        RAISE noroom;
 16     ELSE
 17        UPDATE myHotel
 18        SET resident_count = residentcount + 1
 19        WHERE room_id = roomin;
 20        COMMIT;
 21        DBMS_OUTPUT.put_line ("Student count:"||residentcount||" in "|| roomname);
 22     END IF;
 23  EXCEPTION
 24     WHEN noroom
 25     THEN
 26        DBMS_OUTPUT.put_line ("There is no room in " || roomname);
 27     WHEN OTHERS
 28     THEN
 29        DBMS_OUTPUT.put_line ("Error " || SQLERRM || " occurred.");
 30  END;
 31  /
Procedure created.
SQL>
SQL> show errors
No errors.
SQL>
SQL> drop table myHotel;
Table dropped.
SQL>
SQL>
SQL>
SQL>

Use exception handler to mark success flag

    
SQL> CREATE TABLE emp (
  2    emp_id               NUMBER,
  3    ename             VARCHAR2(40),
  4    hire_date        DATE DEFAULT sysdate,
  5    end_date DATE,
  6    rate     NUMBER(5,2),
  7    CONSTRAINT emp_pk    PRIMARY KEY (emp_id)
  8  );
Table created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (101, "Mary", to_date("15-Nov-1961","dd-mon-yyyy"),null,169);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (102, "Tom", to_date("16-Sep-1964","dd-mon-yyyy"),to_date("5-May-2004","dd-mon-yyyy"),135);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (104, "Peter", to_date("29-Dec-1987","dd-mon-yyyy"),to_date("1-Apr-2004","dd-mon-yyyy"),99);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (105, "Mike", to_date("15-Jun-2004","dd-mon-yyyy"),null,121);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (107, "Less", to_date("2-Jan-2004","dd-mon-yyyy"),null,45);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (108, "Park", to_date("1-Mar-1994","dd-mon-yyyy"),to_date("15-Nov-2004","dd-mon-yyyy"),220);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (110, "Ink", to_date("4-Apr-2004","dd-mon-yyyy"),to_date("30-Sep-2004","dd-mon-yyyy"),84);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (111, "Tike", to_date("23-Aug-1976","dd-mon-yyyy"),null,100);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (112, "Inn", to_date("15-Nov-1961","dd-mon-yyyy"),to_date("4-Apr-2004","dd-mon-yyyy"),70);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (113, "Kate", to_date("3-Mar-2004","dd-mon-yyyy"),to_date("31-Oct-2004","dd-mon-yyyy"),300);
1 row created.
SQL>
SQL> DECLARE
  2    success_flag BOOLEAN;
  3  BEGIN
  4    BEGIN
  5      UPDATE emp
  6         SET rate = rate * 1.10;
  7      success_flag := TRUE;
  8    EXCEPTION
  9      WHEN OTHERS THEN
 10        success_flag := false;
 11    END;
 12
 13    IF success_flag THEN
 14      COMMIT;
 15    ELSE
 16      ROLLBACK;
 17      DBMS_OUTPUT.PUT_LINE("The UPDATE failed.");
 18      RAISE_APPLICATION_ERROR (-20000,
 19     "The UPDATE of emp billing rates failed.");
 20    END IF;
 21  END;
 22  /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> drop table emp;
Table dropped.