Oracle PL/SQL/PL SQL/Exception Handle
Содержание
- 1 Catch all exceptions
- 2 Catch user-defined exception
- 3 Check exception type
- 4 Check zero divide exception
- 5 Combines declaring an EXCEPTION variable
- 6 Deal with multiple exception branches
- 7 declaration exception
- 8 Different Values of SQLCODE and SQLERRM
- 9 Error Handling Call
- 10 Error-handling features of PL/SQL: log exception
- 11 handle exception of duplicate value on index
- 12 Handle update exception
- 13 Insert error message to a table in exception handler
- 14 Mapping a user-defined error code to an EXCEPTION variable
- 15 PLS-483 error: Duplicate Handlers
- 16 Raise exception in if statement
- 17 Sub block in exception section
- 18 The OTHERS Exception Handler
- 19 The scope of exceptions.
- 20 This script demonstrates the EXCEPTION_INIT pragma.
- 21 Using PRAGMA EXCEPTION_INIT
- 22 when other exceptions then
- 23 when other then not user-defined exception
Catch all exceptions
SQL>
SQL> SET ECHO ON
SQL> DECLARE
2 X VARCHAR2(12) := "Hello World!";
3 BEGIN
4 DBMS_OUTPUT.PUT_LINE(X);
5 EXCEPTION
6 WHEN OTHERS THEN
7 DBMS_OUTPUT.PUT_LINE("An error occurred.");
8 END;
9 /
Hello World!
SQL>
SQL>
Catch user-defined 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>
Check exception type
SQL>
SQL> DECLARE
2 num_a NUMBER := 6;
3 num_b NUMBER;
4 BEGIN
5 num_b := 0;
6 num_a := num_a / num_b;
7 num_b := 7;
8 dbms_output.put_line(" Value of num_b " || num_b);
9 EXCEPTION
10 WHEN ZERO_DIVIDE
11 THEN
12 dbms_output.put_line("Trying to divide by zero");
13 dbms_output.put_line(" Value of num_a " || num_a);
14 dbms_output.put_line(" Value of num_b " || num_b);
15 END;
16 /
Trying to divide by zero
Value of num_a 6
Value of num_b 0
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>
Check zero divide exception
SQL> CREATE TABLE EMP(
2 EMPNO NUMBER(4) NOT NULL,
3 ENAME VARCHAR2(10),
4 JOB VARCHAR2(9),
5 MGR NUMBER(4),
6 HIREDATE DATE,
7 SAL NUMBER(7, 2),
8 COMM NUMBER(7, 2),
9 DEPTNO NUMBER(2)
10 );
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> create or replace procedure PROCESS_EMP is
2 cursor C is select empno, sal, comm from emp;
3 newSalary number := 10000;
4 begin
5 for i in c loop
6 newSalary := newSalary + i.rum / i.sal;
7 end loop;
8 exception
9 when zero_divide then
10 if C%ISOPEN then
11 close C;
12 end if;
13 end;
14 /
Procedure created.
SQL> drop table emp;
Table dropped.
Combines declaring an EXCEPTION variable
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>
Deal with multiple exception branches
-- multiple exception branches
set echo on;
set verify off;
set define "&";
prompt "Input degree (numerically in degrees F)?:";
/
accept temp default "100";
/
declare
too_hot exception;
too_cold exception;
begin
case
when "&temp" < 90.00 then raise too_cold;
when "&temp" > 140.00 then raise too_hot;
else null;
end case;
dbms_output.put_line("The temperature is just right");
exception
when VALUE_ERROR then
dbms_output.put_line("Please enter a numeric temperature (like 100)");
when too_hot then
dbms_output.put_line("The porridge is way too hot...");
when too_cold then
dbms_output.put_line("The porridge is way too cold...");
end;
/
declaration exception
SQL>
SQL>
SQL> declare
2 myNumber number default "MY NUMBER";
3 begin
4 null;
5 exception
6 when OTHERS then
7 dbms_output.put_line("Exception caught");
8 raise;
9 end;
10 /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 2
SQL>
Different Values of SQLCODE and SQLERRM
SQL>
SQL> CREATE TABLE myLogTable (
2 code NUMBER,
3 message VARCHAR2(200),
4 info VARCHAR2(100)
5 );
Table created.
SQL>
SQL>
SQL> DECLARE
2 v_ErrorText myLogTable.message%TYPE;
3 BEGIN
4 /* SQLERRM(0) */
5 v_ErrorText := SUBSTR(SQLERRM(0), 1, 200);
6 INSERT INTO myLogTable (code, message, info)
7 VALUES (0, v_ErrorText, "SQLERRM(0)");
8
9 /* SQLERRM(100) */
10 v_ErrorText := SUBSTR(SQLERRM(100), 1, 200);
11 INSERT INTO myLogTable (code, message, info)
12 VALUES (100, v_ErrorText, "SQLERRM(100)");
13
14 /* SQLERRM(10) */
15 v_ErrorText := SUBSTR(SQLERRM(10), 1, 200);
16 INSERT INTO myLogTable (code, message, info)
17 VALUES (10, v_ErrorText, "SQLERRM(10)");
18
19 /* SQLERRM with no argument */
20 v_ErrorText := SUBSTR(SQLERRM, 1, 200);
21 INSERT INTO myLogTable (code, message, info)
22 VALUES (NULL, v_ErrorText, "SQLERRM with no argument");
23
24 /* SQLERRM(-1) */
25 v_ErrorText := SUBSTR(SQLERRM(-1), 1, 200);
26 INSERT INTO myLogTable (code, message, info)
27 VALUES (-1, v_ErrorText, "SQLERRM(-1)");
28
29 /* SQLERRM(-54) */
30 v_ErrorText := SUBSTR(SQLERRM(-54), 1, 200);
31 INSERT INTO myLogTable (code, message, info)
32 VALUES (-54, v_ErrorText, "SQLERRM(-54)");
33
34 END;
35 /
PL/SQL procedure successfully completed.
SQL>
SQL> select * from myLogTable;
CODE MESSAGE
---------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
INFO
----------------------------------------------------------------------------------------------------
0 ORA-0000: normal, successful completion
SQLERRM(0)
100 ORA-01403: no data found
SQLERRM(100)
10 -10: non-ORACLE exception
SQLERRM(10)
CODE MESSAGE
---------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
INFO
----------------------------------------------------------------------------------------------------
ORA-0000: normal, successful completion
SQLERRM with no argument
-1 ORA-00001: unique constraint (.) violated
SQLERRM(-1)
-54 ORA-00054: resource busy and acquire with NOWAIT specified
SQLERRM(-54)
6 rows selected.
SQL>
SQL> drop table myLogTable;
Table dropped.
SQL>
Error Handling Call
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>
Error-handling features of PL/SQL: log exception
SQL>
SQL> CREATE TABLE myLogTable (
2 code NUMBER,
3 message VARCHAR2(200),
4 info VARCHAR2(100)
5 );
Table created.
SQL>
SQL>
SQL>
SQL> DECLARE
2 v_ErrorCode NUMBER; -- Code for the error
3 v_ErrorMsg VARCHAR2(200); -- Message text for the error
4 v_CurrentUser VARCHAR2(8); -- Current database user
5 v_Information VARCHAR2(100); -- Information about the error
6 BEGIN
7 null;
8 EXCEPTION
9 WHEN OTHERS THEN
10 v_ErrorCode := SQLCODE;
11 v_ErrorMsg := SQLERRM;
12 v_CurrentUser := USER;
13 v_Information := "Error encountered on " || TO_CHAR(SYSDATE) || " by database user " || v_CurrentUser;
14 INSERT INTO myLogTable (code, message, info) VALUES (v_ErrorCode, v_ErrorMsg, v_Information);
15 END;
16 /
PL/SQL procedure successfully completed.
SQL>
SQL> select * from myLogTable;
no rows selected
SQL>
SQL> drop table myLogTable;
Table dropped.
SQL>
SQL>
handle exception of duplicate value on index
SQL>
SQL>
SQL> CREATE TABLE departments
2 (department_id number(10) not null,
3 department_name varchar2(50) not null,
4 CONSTRAINT departments_pk PRIMARY KEY (department_id)
5 );
Table created.
SQL>
SQL>
SQL>
SQL> insert into departments ( department_id, department_name )
2 values( 1, "Data Group" );
1 row created.
SQL>
SQL> insert into departments ( department_id, department_name )
2 values( 2, "Purchasing" );
1 row created.
SQL>
SQL> insert into departments ( department_id, department_name )
2 values( 3, "Call Center" );
1 row created.
SQL>
SQL> insert into departments ( department_id, department_name )
2 values( 4, "Communication" );
1 row created.
SQL>
SQL>
SQL> declare
2 l_dept departments%rowtype;
3 begin
4 l_dept.department_id := 100;
5 l_dept.department_name := "Tech Dudes";
6 insert into departments ( department_id, department_name )
7 values( l_dept.department_id, l_dept.department_name );
8 exception
9 when DUP_VAL_ON_INDEX then
10 dbms_output.put_line("DUP_VAL_ON_INDEX exception.");
11 dbms_output.put_line("This is where we""d write out own handler code.");
12 end;
13 /
PL/SQL procedure successfully completed.
SQL>
SQL> drop table departments;
Table dropped.
SQL>
SQL>
Handle update exception
set define "&"
set verify off
set serveroutput on
declare
invalid_column_name exception;
pragma exception_init(invalid_column_name, -904);
l_update_text varchar2(100) :=
"update &&table_name set &&updated_column_name = "":a"" where &&key_column_name = :a";
begin
execute immediate l_update_text
using "&update_column_value", &key_column_value;
exception
when INVALID_COLUMN_NAME then
dbms_output.put("ERROR! You entered an invalid column name ");
dbms_output.put("(&updated_column_name or &key_column_name). Please ");
dbms_output.put_line("check your table definition and try again");
end;
/
Insert error message to a table in exception handler
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>
SQL> CREATE TABLE log_table(
2 code VARCHAR2(100),
3 message VARCHAR2(100),
4 info VARCHAR2(100));
Table created.
SQL> DECLARE
2
3 e_Duplicateemp EXCEPTION;
4
5
6 v_emp1 book.emp1%TYPE;
7 v_emp2 book.emp2%TYPE;
8 v_emp3 book.emp3%TYPE;
9
10 BEGIN
11 SELECT emp1, emp2, emp3 INTO v_emp1, v_emp2, v_emp3 FROM book WHERE title = "XML";
12
13 IF (v_emp1 = v_emp2) OR (v_emp1 = v_emp3) THEN
14 RAISE e_Duplicateemp;
15 END IF;
16 EXCEPTION
17 WHEN e_Duplicateemp THEN
18 INSERT INTO log_table (info) VALUES ("XML has duplicate emp");
19 WHEN OTHERS THEN
20 INSERT INTO log_table (code, message, info) VALUES(NULL, SUBSTR(DBMS_UTILITY.FORMAT_ERROR_STACK, 1, 200),"Oracle error occurred");
21 END;
22 /
PL/SQL procedure successfully completed.
SQL> drop table book;
Table dropped.
SQL> drop table log_table;
Table dropped.
Mapping a user-defined error code to an EXCEPTION variable
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>
PLS-483 error: Duplicate Handlers
SQL>
SQL> CREATE TABLE myLogTable (
2 code NUMBER,
3 message VARCHAR2(200),
4 info VARCHAR2(100)
5 );
Table created.
SQL>
SQL>
SQL> DECLARE
2 e_Exception1 EXCEPTION;
3 e_Exception2 EXCEPTION;
4 BEGIN
5 RAISE e_Exception1;
6 EXCEPTION
7 WHEN e_Exception2 THEN
8 INSERT INTO myLogTable (info)
9 VALUES ("Handler 1 executed!");
10 WHEN e_Exception1 THEN
11 INSERT INTO myLogTable (info)
12 VALUES ("Handler 3 executed!");
13 WHEN e_Exception1 OR e_Exception2 THEN
14 INSERT INTO myLogTable (info)
15 VALUES ("Handler 4 executed!");
16 END;
17 /
WHEN e_Exception1 OR e_Exception2 THEN
*
ERROR at line 13:
ORA-06550: line 13, column 3:
PLS-00483: exception "E_EXCEPTION2" may appear in at most one exception handler in this block
ORA-06550: line 0, column 0:
PL/SQL: Compilation unit analysis terminated
SQL>
SQL> drop table myLogTable;
Table dropped.
SQL>
Raise exception in if statement
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> CREATE TABLE log_table(
2 info VARCHAR2(100));
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>
SQL>
SQL> DECLARE
2
3 e_Duplicateemp EXCEPTION;
4
5
6 v_emp1 book.emp1%TYPE;
7 v_emp2 book.emp2%TYPE;
8 v_emp3 book.emp3%TYPE;
9 BEGIN
10 SELECT emp1, emp2, emp3 INTO v_emp1, v_emp2, v_emp3 FROM book WHERE title = "XML";
11
12 IF (v_emp1 = v_emp2) OR (v_emp1 = v_emp3) THEN
13 RAISE e_Duplicateemp;
14 END IF;
15 EXCEPTION
16 WHEN e_Duplicateemp THEN
17 INSERT INTO log_table (info)VALUES ("XML has duplicate emp");
18 WHEN OTHERS THEN
19 INSERT INTO log_table (info) VALUES ("Another error occurred");
20 END;
21 /
PL/SQL procedure successfully completed.
SQL>
SQL> drop table log_table;
Table dropped.
SQL>
SQL> drop table book;
Table dropped.
Sub block in exception section
SQL>
SQL> DECLARE
2
3 num_a NUMBER := 6;
4 num_b NUMBER;
5
6 BEGIN
7 num_b := 0;
8 num_a := num_a / num_b;
9 num_b := 7;
10 dbms_output.put_line(" Value of num_b " || num_b);
11 EXCEPTION
12 WHEN ZERO_DIVIDE THEN
13 DECLARE
14 err_num NUMBER := SQLCODE;
15 err_msg VARCHAR2(512) := SQLERRM;
16 BEGIN
17 dbms_output.put_line("ORA Error Number " || err_num );
18 dbms_output.put_line("ORA Error message " || err_msg);
19 dbms_output.put_line(" Value of num_a " || num_a);
20 dbms_output.put_line(" Value of num_b " || num_b);
21 END;
22 END;
23 /
ORA Error Number -1476
ORA Error message ORA-01476: divisor is equal to zero
Value of num_a 6
Value of num_b 0
PL/SQL procedure successfully completed.
The OTHERS Exception Handler
SQL>
SQL> CREATE TABLE lecturer (
2 id NUMBER(5) PRIMARY KEY,
3 first_name VARCHAR2(20),
4 last_name VARCHAR2(20),
5 major VARCHAR2(30),
6 current_credits NUMBER(3)
7 );
Table created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10001, "Scott", "Lawson","Computer Science", 11);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major, current_credits)
2 VALUES (10002, "Mar", "Wells","History", 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10003, "Jone", "Bliss","Computer Science", 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10004, "Man", "Kyte","Economics", 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10005, "Pat", "Poll","History", 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10006, "Tim", "Viper","History", 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10007, "Barbara", "Blues","Economics", 7);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10008, "David", "Large","Music", 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10009, "Chris", "Elegant","Nutrition", 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10010, "Rose", "Bond","Music", 7);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10011, "Rita", "Johnson","Nutrition", 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10012, "Sharon", "Clear","Computer Science", 3);
1 row created.
SQL>
SQL>
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> CREATE TABLE myLogTable (
2 code NUMBER,
3 message VARCHAR2(200),
4 info VARCHAR2(100)
5 );
Table created.
SQL>
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 EXCEPTION
14 WHEN myException THEN
15 INSERT INTO myLogTable (info) VALUES ("History 101 has " || studentCount ||
16 "lecturer: max allowed is " || studentMax);
17 WHEN OTHERS THEN
18 INSERT INTO myLogTable (info) VALUES ("Another error occurred");
19 END;
20 /
PL/SQL procedure successfully completed.
SQL>
SQL> drop table lecturer;
Table dropped.
SQL> drop table session;
Table dropped.
SQL> drop table myLogTable;
Table dropped.
SQL>
The scope of exceptions.
SQL>
SQL> BEGIN
2 DECLARE
3 myException EXCEPTION;
4 BEGIN
5 RAISE myException;
6 END;
7 EXCEPTION
8 WHEN OTHERS THEN
9 RAISE;
10 END;
11 /
BEGIN
*
ERROR at line 1:
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at line 9
SQL>
This script demonstrates the EXCEPTION_INIT pragma.
SQL> CREATE TABLE emp (
2 id NUMBER PRIMARY KEY,
3 fname VARCHAR2(50),
4 lname VARCHAR2(50)
5 );
Table created.
SQL>
SQL>
SQL>
SQL> CREATE TABLE log_table(
2 code VARCHAR2(100),
3 message VARCHAR2(100),
4 info VARCHAR2(100));
Table created.
SQL>
SQL> DECLARE
2 e_MissingNull EXCEPTION;
3 PRAGMA EXCEPTION_INIT(e_MissingNull, -1400);
4 BEGIN
5 INSERT INTO emp (id) VALUES (NULL);
6 EXCEPTION
7 WHEN e_MissingNull then
8 INSERT INTO log_table (info) VALUES ("ORA-1400 occurred");
9 END;
10 /
PL/SQL procedure successfully completed.
SQL>
SQL> SELECT info FROM log_table;
INFO
--------------------------------------------------------------------------------
ORA-1400 occurred
1 row selected.
SQL> drop table log_table;
Table dropped.
SQL> drop table emp;
Table dropped.
Using PRAGMA EXCEPTION_INIT
SQL>
SQL> CREATE TABLE myLogTable (
2 code NUMBER,
3 message VARCHAR2(200),
4 info VARCHAR2(100)
5 );
Table created.
SQL>
SQL>
SQL> CREATE TABLE lecturer (
2 id NUMBER(5) PRIMARY KEY,
3 first_name VARCHAR2(20),
4 last_name VARCHAR2(20),
5 major VARCHAR2(30),
6 current_credits NUMBER(3)
7 );
Table created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10001, "Scott", "Lawson","Computer Science", 11);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major, current_credits)
2 VALUES (10002, "Mar", "Wells","History", 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10003, "Jone", "Bliss","Computer Science", 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10004, "Man", "Kyte","Economics", 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10005, "Pat", "Poll","History", 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10006, "Tim", "Viper","History", 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10007, "Barbara", "Blues","Economics", 7);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10008, "David", "Large","Music", 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10009, "Chris", "Elegant","Nutrition", 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10010, "Rose", "Bond","Music", 7);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10011, "Rita", "Johnson","Nutrition", 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10012, "Sharon", "Clear","Computer Science", 3);
1 row created.
SQL> DECLARE
2 e_MissingNull EXCEPTION;
3 PRAGMA EXCEPTION_INIT(e_MissingNull, -1400);
4 BEGIN
5 INSERT INTO lecturer (id) VALUES (NULL);
6 EXCEPTION
7 WHEN e_MissingNull then
8 INSERT INTO myLogTable (info) VALUES ("ORA-1400 occurred");
9 END;
10 /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> select * from myLogTable;
CODE MESSAGE
---------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
INFO
----------------------------------------------------------------------------------------------------
ORA-1400 occurred
SQL>
SQL> drop table myLogTable;
Table dropped.
SQL> drop table lecturer;
Table dropped.
SQL>
SQL>
SQL>
when other exceptions then
SQL>
SQL> -- when others then
SQL>
SQL> set serverout on
SQL>
SQL> declare
2 PARENT_ERROR exception;
3 begin
4 declare
5 CHILD_ERROR exception;
6 begin
7 raise CHILD_ERROR;
8 exception
9 when CHILD_ERROR then
10 dbms_output.put_line("nested block exception handler");
11 raise;
12 end;
13 exception
14 when PARENT_ERROR then
15 dbms_output.put_line("parent block exception handler");
16
17 when OTHERS then
18 dbms_output.put_line("Caught the OTHERS exception");
19 raise;
20
21 end;
22 /
nested block exception handler
Caught the OTHERS exception
declare
*
ERROR at line 1:
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at line 19
when other then not user-defined exception
SQL>
SQL> set serverout on
SQL>
SQL> declare
2 PARENT_ERROR exception;
3 begin
4 declare
5 CHILD_ERROR exception;
6 begin
7 raise CHILD_ERROR;
8 exception
9 when CHILD_ERROR then
10 dbms_output.put_line("nested block exception handler");
11 raise;
12 end;
13 exception
14 when PARENT_ERROR then
15 dbms_output.put_line("parent block exception handler");
16
17 when OTHERS then
18 dbms_output.put_line("Caught the OTHERS exception");
19 raise;
20
21 end;
22 /
nested block exception handler
Caught the OTHERS exception
declare
*
ERROR at line 1:
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at line 19