SQL>
SQL>
SQL> CREATE TABLE emp (
2 empID INT NOT NULL PRIMARY KEY,
3 Name VARCHAR(50) NOT NULL);
Table created.
SQL> INSERT INTO emp (empID,Name) VALUES (1,'Tom');
1 row created.
SQL> INSERT INTO emp (empID,Name) VALUES (2,'Jack');
1 row created.
SQL> INSERT INTO emp (empID,Name) VALUES (3,'Mary');
1 row created.
SQL> INSERT INTO emp (empID,Name) VALUES (4,'Bill');
1 row created.
SQL> INSERT INTO emp (empID,Name) VALUES (5,'Cat');
1 row created.
SQL> INSERT INTO emp (empID,Name) VALUES (6,'Victor');
1 row created.
SQL> CREATE OR REPLACE PROCEDURE ErrorTest(i_StudID IN INT,i_StudName IN VARCHAR)
2 AS
3 UnluckyNumber EXCEPTION;
4 BEGIN
5 IF i_StudID = 13 THEN
6 RAISE UnluckyNumber;
7 END IF;
8 INSERT INTO emp VALUES (i_StudID, i_StudName);
9 EXCEPTION
10 WHEN DUP_VAL_ON_INDEX THEN
11 dbms_output.put_line('An emp already exists with ID ' || i_StudID);
12 WHEN UnluckyNumber THEN
13 dbms_output.put_line('Can''t insert an emp with an unlucky ID');
14 END;
15 /
Procedure created.
SQL> CALL ErrorTest(10, 'Jason Fields');
Call completed.
SQL> CALL ErrorTest(13, 'Charles Ives');
Can't insert an emp with an unlucky ID
Call completed.
SQL>
SQL>
SQL> drop table emp;
Table dropped.
SQL>
SQL>