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>

Related examples in the same category

1.	Raise your own exception
2.	Raising an exception
3.	Raising NO_DATA_FOUND Exception
4.	RAISE_APPLICATION_ERROR
5.	Raise different exception depends on value input
6.	Raise exceptions for wrong parameters
7.	Print out an error message
8.	Raises and manages a standard anonymous block PL/SQL program error.
9.	Setting the message dynamically

This procedure demonstrates the use of RAISE_APPLICATION_ERROR. : Raise « PL SQL « Oracle PL / SQL

Related examples in the same category