Oracle PL/SQL/PL SQL/Raise

Print out an error message

    
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>

RAISE_APPLICATION_ERROR

   
SQL>
SQL> CREATE TABLE myStudent (
  2    student_id NUMBER(5) NOT NULL,
  3    department CHAR(3)   NOT NULL,
  4    course     NUMBER(3) NOT NULL,
  5    grade      CHAR(1)
  6    );
Table created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
  2                           VALUES (10000, "CS", 102, "A");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
  2                           VALUES (10002, "CS", 102, "B");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
  2                           VALUES (10003, "CS", 102, "C");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
  2                           VALUES (10000, "HIS", 101, "A");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
  2                           VALUES (10001, "HIS", 101, "B");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
  2                           VALUES (10002, "HIS", 101, "B");
1 row created.
SQL> CREATE OR REPLACE FUNCTION AverageGrade (
  2    p_Department IN myStudent.department%TYPE,
  3    p_Course IN myStudent.course%TYPE) RETURN CHAR AS
  4
  5    v_AverageGrade CHAR(1);
  6    v_NumericGrade NUMBER;
  7    v_Numberlecturer NUMBER;
  8
  9    CURSOR c_Grades IS
 10      SELECT grade
 11        FROM myStudent
 12        WHERE department = p_Department
 13        AND course = p_Course;
 14  BEGIN
 15    SELECT COUNT(*)
 16      INTO v_Numberlecturer
 17      FROM myStudent
 18      WHERE department = p_Department
 19        AND course = p_Course;
 20
 21    IF v_Numberlecturer = 0 THEN
 22      RAISE_APPLICATION_ERROR(-20001, "No lecturer registered for " ||
 23        p_Department || " " || p_Course);
 24    END IF;
 25
 26    SELECT AVG(DECODE(grade, "A", 5,
 27                             "B", 4,
 28                             "C", 3,
 29                             "D", 2,
 30                             "E", 1))
 31      INTO v_NumericGrade
 32      FROM myStudent
 33      WHERE department = p_Department
 34      AND course = p_Course;
 35
 36    SELECT DECODE(ROUND(v_NumericGrade), 5, "A",
 37                                         4, "B",
 38                                         3, "C",
 39                                         2, "D",
 40                                         1, "E")
 41      INTO v_AverageGrade
 42      FROM dual;
 43
 44    RETURN v_AverageGrade;
 45  END AverageGrade;
 46  /
Function created.
SQL>
SQL> drop table myStudent;
Table dropped.
SQL>

Raise different exception depends on value input

   
SQL>
SQL> CREATE OR REPLACE PROCEDURE RaiseIt(p_Exception IN NUMBER) AS
  2    e_MyException EXCEPTION;
  3  BEGIN
  4    IF p_Exception = 0 THEN
  5      NULL;
  6    ELSIF p_Exception < 0 THEN
  7      RAISE e_MyException;
  8    ELSIF p_Exception = 1001 THEN
  9      RAISE INVALID_CURSOR;
 10    ELSIF p_Exception = 1403 THEN
 11      RAISE NO_DATA_FOUND;
 12    ELSIF p_Exception = 6502 THEN
 13      RAISE VALUE_ERROR;
 14    ELSE
 15      RAISE_APPLICATION_ERROR(-20001, "Exception " || p_Exception);
 16    END IF;
 17  END RaiseIt;
 18  /
Procedure created.
SQL> show errors
No errors.
SQL>

Raise exceptions for wrong parameters

  
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,"An error has occurred inserting an employee.");
 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>
SQL>

Raises and manages a standard anonymous block PL/SQL program error.

    
SQL>
SQL> SET SERVEROUTPUT ON SIZE 1000000
SQL>
SQL> 
SQL> DECLARE
  2
  3    
  4    my_error          EXCEPTION;
  5
  6  BEGIN
  7
  8    
  9    RAISE my_error;
 10
 11  EXCEPTION
 12
 13    WHEN others THEN
 14      dbms_output.put_line("RAISE my_error"||CHR(10)||"SQLCODE ["||SQLCODE||"]"||CHR(10)||"SQLERRM ["||SQLERRM||"]");
 15
 16  END;
 17  /
RAISE my_error
SQLCODE [1]
SQLERRM [User-Defined Exception]
PL/SQL procedure successfully completed.
SQL>

Raise your own exception

   
SQL>
SQL>
SQL> set echo off
SQL> set verify off
SQL> set define "&"
SQL>
SQL> prompt "degrees F?:"
"degrees F?:"
SQL> accept temp default "100"
SQL> declare
  2    porridge_too_hot  exception;
  3    porridge_too_cold exception;
  4  begin
  5    case
  6      when "&temp" < 90.00 then raise porridge_too_cold;
  7      when "&temp" > 140.00 then raise porridge_too_hot;
  8      else null;
  9    end case;
 10
 11    dbms_output.put_line("just right");
 12
 13  exception
 14    when VALUE_ERROR then
 15      dbms_output.put_line("Please enter a numeric temperature (like 100)");
 16
 17    when porridge_too_hot then
 18      dbms_output.put_line("way too hot...");
 19
 20    when porridge_too_cold then
 21      dbms_output.put_line("way too cold...");
 22  end;
 23  /
just right
PL/SQL procedure successfully completed.
SQL>

Raising an exception

   
SQL> CREATE TABLE session (
  2    department       CHAR(3),
  3    course           NUMBER(3),
  4    description      VARCHAR2(2000),
  5    max_lecturer     NUMBER(3),
  6    current_lecturer NUMBER(3),
  7    num_credits      NUMBER(1),
  8    room_id          NUMBER(5)
  9    );
Table created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("HIS", 101, "History 101", 30, 11, 4, 20000);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("HIS", 301, "History 301", 30, 0, 4, 20004);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("CS", 101, "Computer Science 101", 50, 0, 4, 20001);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("ECN", 203, "Economics 203", 15, 0, 3, 20002);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("CS", 102, "Computer Science 102", 35, 3, 4, 20003);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("MUS", 410, "Music 410", 5, 4, 3, 20005);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("ECN", 101, "Economics 101", 50, 0, 4, 20007);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("NUT", 307, "Nutrition 307", 20, 2, 4, 20008);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("MUS", 100, "Music 100", 100, 0, 3, NULL);
1 row created.
SQL>
SQL>
SQL> DECLARE
  2    myException EXCEPTION;
  3    studentCount NUMBER(3);
  4    studentMax NUMBER(3);
  5  BEGIN
  6    SELECT current_lecturer, max_lecturer
  7      INTO studentCount, studentMax
  8      FROM session
  9      WHERE department = "HIS" AND course = 101;
 10    IF studentCount > studentMax THEN
 11      RAISE myException;
 12    END IF;
 13  END;
 14  /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> drop table session;
Table dropped.
SQL>
SQL>

Raising NO_DATA_FOUND Exception

   
SQL>
SQL> DECLARE
  2    TYPE t_NumberTableType IS TABLE OF NUMBER
  3      INDEX BY BINARY_INTEGER;
  4    v_NumberTable t_NumberTableType;
  5    v_TempVar NUMBER;
  6  BEGIN
  7    v_TempVar := v_NumberTable(1);
  8  END;
  9  /
DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 7

SQL>

Setting the message dynamically

    
SQL>
SQL> DECLARE
  2     e EXCEPTION;
  3    PRAGMA EXCEPTION_INIT(e,-20001);
  4  BEGIN
  5    RAISE_APPLICATION_ERROR(-20001,"A less than original message.");
  6  EXCEPTION
  7    WHEN e THEN
  8      dbms_output.put_line(SQLERRM);
  9  END;
 10  /
ORA-20001: A less than original message.
PL/SQL procedure successfully completed.
SQL>

This procedure demonstrates the use of RAISE_APPLICATION_ERROR.

    
SQL> CREATE TABLE emp (
  2    id         NUMBER PRIMARY KEY,
  3    fname VARCHAR2(50),
  4    lname  VARCHAR2(50)
  5  );
Table created.
SQL>
SQL> INSERT INTO emp (id, fname, lname)VALUES (1, "A", "B");
1 row created.
SQL> INSERT INTO emp (id, fname, lname)VALUES (2, "C", "D");
1 row created.
SQL> INSERT INTO emp (id, fname, lname)VALUES (3, "Enn", "F");
1 row created.
SQL> INSERT INTO emp (id, fname, lname)VALUES (4, "G", "H");
1 row created.
SQL> INSERT INTO emp (id, fname, lname)VALUES (5, "G", "Z");
1 row created.
SQL>
SQL> CREATE TABLE book (
  2    isbn      CHAR(10) PRIMARY KEY,
  3    category  VARCHAR2(20),
  4    title     VARCHAR2(100),
  5    num_pages NUMBER,
  6    price     NUMBER,
  7    copyright NUMBER(4),
  8    emp1   NUMBER,
  9    emp2   NUMBER,
 10    emp3   NUMBER
 11  );
Table created.
SQL>
SQL> INSERT INTO book (isbn, category, title, num_pages, price, copyright, emp1, emp2, emp3)
  2             VALUES ("1", "Database", "Oracle", 563, 39.99, 1999, 1, 2, 3);
1 row created.
SQL> INSERT INTO book (isbn, category, title, num_pages, price, copyright, emp1, emp2)
  2             VALUES ("2", "Database", "MySQL", 765, 44.99, 1999, 4, 5);
1 row created.
SQL> INSERT INTO book (isbn, category, title, num_pages, price, copyright, emp1, emp2, emp3)
  2             VALUES ("3", "Database", "SQL Server", 404, 39.99, 2001, 6, 7, 8);
1 row created.
SQL> INSERT INTO book (isbn, category, title, num_pages, price, copyright, emp1, emp2, emp3)
  2             VALUES ("4", "Database", "SQL", 535, 39.99, 2002, 4, 5, 9);
1 row created.
SQL> INSERT INTO book (isbn, category, title, num_pages, price, copyright, emp1, emp2)
  2             VALUES ("5", "Database", "Java", 487, 39.99, 2002, 10, 11);
1 row created.
SQL> INSERT INTO book (isbn, category, title, num_pages, price, copyright, emp1, emp2)
  2             VALUES ("6", "Database", "JDBC", 592, 39.99, 2002, 12, 13);
1 row created.
SQL> INSERT INTO book (isbn, category, title, num_pages, price, copyright, emp1, emp2, emp3)
  2             VALUES ("7", "Database", "XML", 500, 39.99, 2002, 1, 2, 3);
1 row created.
SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE Verifyemp(p_emp1 IN book.emp1%TYPE,p_emp2 IN book.emp2%TYPE,p_emp3 IN book.emp3%TYPE) AS
  2
  3    v_empCount NUMBER;
  4  BEGIN
  5    IF p_emp1 IS NULL THEN
  6      RAISE_APPLICATION_ERROR(-20000, "emp1 cannot be null");
  7    ELSE
  8      SELECT COUNT(*) INTO v_empCount FROM emp WHERE id = p_emp1;
  9      IF v_empCount = 0 THEN
 10        RAISE_APPLICATION_ERROR(-20001,"emp1 " || p_emp1 || " does not exist");
 11      END IF;
 12    END IF;
 13
 14    IF p_emp1 = p_emp2 THEN
 15      RAISE_APPLICATION_ERROR (-20002,"emp1 " || p_emp1 || " and emp2 " || p_emp2 ||" are duplicates");
 16    ELSIF p_emp1 = p_emp3 THEN
 17      RAISE_APPLICATION_ERROR (-20002,"emp1 " || p_emp1 || " and emp3 " || p_emp3 ||" are duplicates");
 18    ELSIF p_emp2 = p_emp3 THEN
 19      RAISE_APPLICATION_ERROR (-20002,"emp2 " || p_emp2 || " and emp3 " || p_emp3 ||" are duplicates");
 20    END IF;
 21  END Verifyemp;
 22  /
Procedure created.

SQL>
SQL> drop table book;
Table dropped.
SQL> drop table emp;
Table dropped.
SQL>