Mega Code Archive
This procedure demonstrates the use of RAISE_APPLICATION_ERROR
<![CDATA[
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>
]]>