Oracle PL/SQL Tutorial/PL SQL Programming/Handle Exception
Содержание
- 1 An example showing continuing program execution after handling exception
- 2 Assigning a Name to Predefined Exception Code
- 3 Catch custom exception
- 4 Check OTHERS exception
- 5 Code with Conditional Control to Avoid an Exception
- 6 Code with Explicit Handler for Predefined Exception
- 7 Code with No Exception Handler
- 8 Error message code and text
- 9 Handling a custom exception
- 10 Handling an Unnamed Exception
- 11 Handling exceptions without halting the program
- 12 NO data found
- 13 Select into statement with exception catch statement
- 14 The OTHERS Exception Handler
- 15 TOO_MANY_ROWS Exception
- 16 Use a nested block to catch exceptions from singleton SELECT.
- 17 Using PRAGMA EXCEPTION_INIT
- 18 Using SQLCODE for error code and SQLERRM for error message
An example showing continuing program execution after handling exception
SQL> create table product(
2 product_id number(4) not null,
3 product_description varchar2(20) not null
4 );
Table created.
SQL>
SQL> insert into product values (1,"Java");
1 row created.
SQL> insert into product values (2,"Oracle");
1 row created.
SQL> insert into product values (3,"C#");
1 row created.
SQL> insert into product values (4,"Javascript");
1 row created.
SQL> insert into product values (5,"Python");
1 row created.
SQL>
SQL>
SQL> DECLARE
2 v_descr VARCHAR2(20);
3 BEGIN
4 BEGIN
5 SELECT product_description
6 INTO v_descr
7 FROM product
8 WHERE product_id =10;
9 dbms_output.put_line(v_descr);
10 EXCEPTION WHEN NO_DATA_FOUND THEN
11 INSERT INTO product VALUES (10,"Assistant");
12 COMMIT;
13 END;
14 BEGIN
15 SELECT product_description
16 INTO v_descr
17 FROM product
18 WHERE product_id =1;
19 dbms_output.put_line(v_descr);
20 EXCEPTION WHEN NO_DATA_FOUND THEN
21 dbms_output.put_line("ERR:Invalid Data for Hierarchy");
22 END;
23 EXCEPTION
24 WHEN OTHERS THEN
25 dbms_output.put_line("ERR:An error occurred with info :"||
26 TO_CHAR(SQLCODE)||" "||SQLERRM);
27 END;
28 /
Java
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> drop table product;
Table dropped.
SQL>
Assigning a Name to Predefined Exception Code
SQL>
SQL>
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 p_updateLoc(i_id NUMBER, i_new_loc VARCHAR2)
2 is
3 v_error NUMBER;
4 e_deadlock_detected exception;
5 pragma exception_init(e_deadlock_detected,-60);
6 begin
7 update employee
8 set city=i_new_loc
9 where id=i_id;
10 exception
11 when e_deadlock_detected then
12 DBMS_OUTPUT.put_line("i_id:"||i_id);
13 DBMS_OUTPUT.put_line("v_error:"||v_error);
14 raise;
15 end;
16 /
Procedure created.
SQL> call p_updateLoc(2,"aa");
Call completed.
SQL>
SQL> select * from employee;
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 aa 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> call p_updateLoc(1,"AAAAAAAAAAAAAAAAAAAAAAAAAAA");
call p_updateLoc(1,"AAAAAAAAAAAAAAAAAAAAAAAAAAA")
*
ERROR at line 1:
ORA-12899: value too large for column "sqle"."EMPLOYEE"."CITY" (actual: 27, maximum: 10)
ORA-06512: at "sqle.P_UPDATELOC", line 7
SQL> select * from employee;
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 aa 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> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>
SQL>
SQL>
Catch custom exception
SQL> set serveroutput on
SQL> DECLARE
2 quantity1 NUMBER := -2;
3 quantity2 NUMBER := 3;
4 total NUMBER := 0;
5 quantity_must_positive EXCEPTION;
6 FUNCTION find_cost (quant NUMBER) RETURN NUMBER IS
7 BEGIN
8 IF (quant > 0)
9 THEN
10 RETURN(quant * 20);
11 ELSE
12 RAISE quantity_must_positive;
13 END IF;
14 END find_cost;
15 BEGIN
16 total := find_cost (quantity2);
17 total := total + find_cost(quantity1);
18 EXCEPTION
19 WHEN quantity_must_positive
20 THEN
21 dbms_output.put_line("Total until now: " || total);
22 dbms_output.put_line("Tried to use negative quantity ");
23 END;
24 /
Total until now: 60
Tried to use negative quantity
PL/SQL procedure successfully completed.
SQL>
SQL>
Check OTHERS exception
SQL>
SQL>
SQL> -- create demo table
SQL> create table Employee(
2 ID VARCHAR2(4 BYTE),
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>
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
SQL>
SQL>
SQL>
SQL> DECLARE
2 v_count NUMBER(10) := 0;
3
4 BEGIN
5
6 SELECT count(1)
7 INTO v_count
8 FROM employee
9 WHERE id = 5;
10
11 IF v_count = 0
12 THEN
13 INSERT INTO employee (id, first_name, last_name)
14 VALUES (5, "Randy", "Stauf");
15 END IF;
16 ROLLBACK;
17 EXCEPTION
18 WHEN OTHERS
19 THEN
20 DBMS_OUTPUT.PUT_LINE(SQLERRM);
21 END;
22 /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
Code with Conditional Control to Avoid an Exception
SQL>
SQL> create or replace function f_get_speed(i_distance NUMBER, i_timeSec NUMBER)
2 return NUMBER
3 is
4 v_out number:=0;
5 begin
6 if i_timeSec!=0 then
7 v_out:= i_distance/ i_timeSec;
8 end if;
9 return v_out;
10 end;
11 /
Function created.
SQL>
SQL>
SQL> declare
2 v_speed NUMBER;
3 begin
4 v_speed:=f_get_speed(10,0);
5 end;
6 /
PL/SQL procedure successfully completed.
SQL>
Code with Explicit Handler for Predefined Exception
SQL>
SQL> create or replace function f_get_speed(i_Distance NUMBER, i_timeSec NUMBER)
2 return NUMBER
3 is
4 v_out NUMBER;
5 begin
6 v_out:= i_distance/i_timeSec;
7 return v_out;
8 exception
9 WHEN ZERO_DIVIDE THEN
10 dbms_output.put_line("Divide by zero in the F_GET_SPEED");
11 return null;
12 end;
13 /
Function created.
SQL>
SQL>
SQL>
SQL> declare
2 v_speed NUMBER;
3 begin
4 v_speed:=f_get_speed(10,0);
5 end;
6 /
Divide by zero in the F_GET_SPEED
PL/SQL procedure successfully completed.
SQL>
Code with No Exception Handler
SQL>
SQL> create or replace function f_get_speed(i_distance NUMBER, i_timeSec NUMBER)
2 return NUMBER
3 is
4 v_out NUMBER;
5 begin
6 v_out:= i_distance/i_timeSec;
7 return v_out;
8 end;
9 /
Function created.
SQL>
SQL> declare
2 v_speed NUMBER;
3 begin
4 v_speed:=f_get_speed(10,0);
5 end;
6 /
declare
*
ERROR at line 1:
ORA-01476: divisor is equal to zero
ORA-06512: at "sqle.F_GET_SPEED", line 6
ORA-06512: at line 4
SQL>
Error message code and text
SQL> DECLARE
2 e_TooManyEmployee EXCEPTION; -- Exception to indicate an error condition
3 v_ErrorCode NUMBER; -- Variable to hold the error message code
4 v_ErrorText VARCHAR2(200); -- Variable to hold the error message text
5
6 BEGIN
7 RAISE e_TooManyEmployee;
8 EXCEPTION
9 WHEN e_TooManyEmployee THEN
10 DBMS_OUTPUT.put_line("e_TooManyEmployee");
11 v_ErrorText := SUBSTR(SQLERRM, 1, 200); -- Note the use of SUBSTR here.
12 DBMS_OUTPUT.put_line(v_ErrorText);
13 /* SQLERRM(0) */
14 v_ErrorText := SUBSTR(SQLERRM(0), 1, 200);
15 DBMS_OUTPUT.put_line(v_ErrorText);
16
17 /* SQLERRM(100) */
18 v_ErrorText := SUBSTR(SQLERRM(100), 1, 200);
19 DBMS_OUTPUT.put_line(v_ErrorText);
20
21 /* SQLERRM(10) */
22 v_ErrorText := SUBSTR(SQLERRM(10), 1, 200);
23 DBMS_OUTPUT.put_line(v_ErrorText);
24
25 /* SQLERRM with no argument */
26 v_ErrorText := SUBSTR(SQLERRM, 1, 200);
27 DBMS_OUTPUT.put_line(v_ErrorText);
28
29 /* SQLERRM(-1) */
30 v_ErrorText := SUBSTR(SQLERRM(-1), 1, 200);
31 DBMS_OUTPUT.put_line(v_ErrorText);
32
33 /* SQLERRM(-54) */
34 v_ErrorText := SUBSTR(SQLERRM(-54), 1, 200);
35 DBMS_OUTPUT.put_line(v_ErrorText);
36
37
38 WHEN OTHERS THEN
39 v_ErrorCode := SQLCODE;
40 END;
41 /
e_TooManyEmployee
User-Defined Exception
ORA-0000: normal, successful completion
ORA-01403: no data found
-10: non-ORACLE exception
User-Defined Exception
ORA-00001: unique constraint (.) violated
ORA-00054: resource busy and acquire with NOWAIT specified
PL/SQL procedure successfully completed.
SQL>
Handling a custom exception
SQL>
SQL>
SQL> DECLARE
2 e_TooManyEmployee EXCEPTION; -- Exception to indicate an error condition
3 BEGIN
4 RAISE e_TooManyEmployee;
5 EXCEPTION
6 WHEN e_TooManyEmployee THEN
7 DBMS_OUTPUT.put_line("e_TooManyEmployee");
8 END;
9 /
e_TooManyEmployee
PL/SQL procedure successfully completed.
SQL>
SQL>
Handling an Unnamed Exception
SQL>
SQL>
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>
SQL> create or replace procedure p_updateLoc(i_id VARCHAR, i_new_loc VARCHAR2)
2 is
3 v_error NUMBER;
4 v_sqlerrm VARCHAR2(4000);
5 begin
6 update employee
7 set city=i_new_loc
8 where id=i_id;
9 exception
10 when others then
11 v_error :=sqlcode;
12 v_sqlerrm := sqlerrm;
13 DBMS_OUTPUT.put_line("i_id:"||i_id);
14 DBMS_OUTPUT.put_line("v_error:"||v_error);
15 DBMS_OUTPUT.put_line("v_sqlerrm:"||v_sqlerrm);
16 if sqlcode=-60 then -- deadlock error is ORA-00060
17 null;
18 else
19 raise;
20 end if;
21 end;
22 /
Procedure created.
SQL> call p_updateLoc("01", "AA");
Call completed.
SQL>
SQL> select * from employee;
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- -------------------- -------------------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 AA 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> call p_updateLoc("02", "AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA");
i_id:02
v_error:-12899
v_sqlerrm:ORA-12899: value too large for column "sqle"."EMPLOYEE"."CITY" (actual: 40, maximum: 10)
call p_updateLoc("02", "AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA")
*
ERROR at line 1:
ORA-12899: value too large for column "sqle"."EMPLOYEE"."CITY" (actual: 40, maximum: 10)
ORA-06512: at "sqle.P_UPDATELOC", line 19
SQL>
SQL> select * from employee;
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- -------------------- -------------------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 AA 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> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>
SQL>
SQL>
Handling exceptions without halting the program
SQL> --
SQL>
SQL>
SQL> create or replace function f_get_speed(i_distance NUMBER, i_timeSec NUMBER)
2 return NUMBER
3 is
4 v_out NUMBER;
5 begin
6
7 begin
8 v_out:= i_distance/i_timeSec;
9 exception
10 when zero_divide then
11 DBMS_OUTPUT.put_line("Divide by zero in the F_GET_SPEED");
12 end;
13 return v_out;
14 end;
15 /
Function created.
SQL>
SQL> select f_get_speed(1, 0) from dual;
F_GET_SPEED(1,0)
----------------
Divide by zero in the F_GET_SPEED
SQL>
NO data found
SQL>
SQL>
SQL> create table employee (
2 id number,
3 employee_type_id number,
4 external_id varchar2(30),
5 first_name varchar2(30),
6 middle_name varchar2(30),
7 last_name varchar2(30),
8 name varchar2(100),
9 birth_date date ,
10 gender_id number );
Table created.
SQL>
SQL>
SQL> create table gender (
2 id number,
3 code varchar2(30),
4 description varchar2(80),
5 active_date date default SYSDATE not null,
6 inactive_date date );
Table created.
SQL>
SQL>
SQL>
SQL> insert into gender ( id, code, description ) values ( 1, "F", "Female" );
1 row created.
SQL> insert into gender ( id, code, description ) values ( 2, "M", "Male" );
1 row created.
SQL> insert into gender ( id, code, description ) values ( 3, "U", "Unknown" );
1 row created.
SQL>
SQL>
SQL>
SQL> set serveroutput on size 1000000;
SQL>
SQL> declare
2
3 d_birth_date employee.birth_date%TYPE;
4 n_gender_id employee.gender_id%TYPE;
5 n_selected number := -1;
6 n_id employee.id%TYPE;
7 v_first_name employee.first_name%TYPE;
8 v_last_name employee.last_name%TYPE;
9 v_middle_name employee.middle_name%TYPE;
10 v_name employee.name%TYPE;
11
12 begin
13 v_first_name := "JOHN";
14 v_middle_name := "J.";
15 v_last_name := "DOUGH";
16 v_name := rtrim(v_last_name||", "||v_first_name||" "||v_middle_name);
17 d_birth_date := to_date("19800101", "YYYYMMDD");
18
19 begin
20 select id into n_gender_id from gender where code = "M";
21 exception
22 when OTHERS then
23 raise_application_error(-20001, SQLERRM||" on select gender");
24 end;
25
26 begin
27 select id
28 into n_id
29 from employee
30 where name = v_name
31 and birth_date = d_birth_date
32 and gender_id = n_gender_id;
33
34 n_selected := sql%rowcount;
35 exception
36 when NO_DATA_FOUND then
37 n_selected := sql%rowcount;
38 DBMS_OUTPUT.PUT_LINE("Caught raised exception NO_DATA_FOUND");
39 when OTHERS then
40 raise_application_error(-20002, SQLERRM||" on select employee");
41 end;
42
43 DBMS_OUTPUT.PUT_LINE(to_char(n_selected)||" row(s) selected.");
44 end;
45 /
Caught raised exception NO_DATA_FOUND
0 row(s) selected.
PL/SQL procedure successfully completed.
SQL>
SQL> drop table gender;
Table dropped.
SQL>
SQL> drop table employee;
Table dropped.
SQL>
Select into statement with exception catch statement
SQL>
SQL>
SQL>
SQL> -- create demo table
SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL primary key,
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>
SQL> SET SERVEROUTPUT ON ESCAPE OFF
SQL>
SQL> DECLARE
2 v_employee EMPLOYEE.FIRST_NAME%TYPE;
3 BEGIN
4
5 -- the first nested block
6 BEGIN
7 SELECT first_name
8 INTO v_employee
9 FROM EMPLOYEE
10 WHERE UPPER(last_name) = "CAT";
11 EXCEPTION
12 WHEN NO_DATA_FOUND
13 THEN
14 DBMS_OUTPUT.PUT_LINE("EXCEPTION HANDLER for nested block 1");
15 DBMS_OUTPUT.PUT_LINE(" ");
16 NULL;
17 END;
18
19 -- the second nested block
20 BEGIN
21 SELECT first_name
22 INTO v_employee
23 FROM EMPLOYEE
24 WHERE UPPER(last_name) = "HARDMAN";
25
26 EXCEPTION
27 WHEN TOO_MANY_ROWS
28 THEN
29 DBMS_OUTPUT.PUT_LINE(" ");
30 DBMS_OUTPUT.PUT_LINE("EXCEPTION HANDLER for nested block 2");
31 DBMS_OUTPUT.PUT_LINE("If this is printing, then the both nested");
32 DBMS_OUTPUT.PUT_LINE("blocks"" exception handler worked!");
33 END;
34 END;
35 /
DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 21
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
The OTHERS Exception Handler
SQL> DECLARE
2 e_TooManyEmployee EXCEPTION; -- Exception to indicate an error condition
3 BEGIN
4 RAISE e_TooManyEmployee;
5 EXCEPTION
6 WHEN e_TooManyEmployee THEN
7 DBMS_OUTPUT.put_line("e_TooManyEmployee");
8 WHEN OTHERS THEN
9 DBMS_OUTPUT.put_line("OTHERS");
10 END;
11 /
e_TooManyEmployee
PL/SQL procedure successfully completed.
SQL>
SQL>
TOO_MANY_ROWS Exception
SQL>
SQL>
SQL>
SQL> create table employee (
2 id number,
3 employee_type_id number,
4 external_id varchar2(30),
5 first_name varchar2(30),
6 middle_name varchar2(30),
7 last_name varchar2(30),
8 name varchar2(100),
9 birth_date date ,
10 gender_id number );
Table created.
SQL>
SQL>
SQL> create table gender (
2 id number,
3 code varchar2(30),
4 description varchar2(80),
5 active_date date default SYSDATE not null,
6 inactive_date date );
Table created.
SQL>
SQL>
SQL>
SQL> insert into gender ( id, code, description ) values ( 1, "F", "Female" );
1 row created.
SQL> insert into gender ( id, code, description ) values ( 2, "M", "Male" );
1 row created.
SQL> insert into gender ( id, code, description ) values ( 3, "U", "Unknown" );
1 row created.
SQL>
SQL>
SQL>
SQL>
SQL> set serveroutput on size 1000000;
SQL>
SQL> declare
2
3 d_birth_date employee.birth_date%TYPE;
4 n_gender_id employee.gender_id%TYPE;
5 n_selected number := -1;
6 n_id employee.id%TYPE;
7 v_first_name employee.first_name%TYPE;
8 v_last_name employee.last_name%TYPE;
9 v_middle_name employee.middle_name%TYPE;
10 v_name employee.name%TYPE;
11
12 begin
13 v_first_name := "JOHN";
14 v_middle_name := "J.";
15 v_last_name := "DOE";
16 v_name := rtrim(v_last_name||", "||v_first_name||" "||v_middle_name);
17 d_birth_date := to_date("19800101", "YYYYMMDD");
18
19 begin
20 select id into n_gender_id from gender where code = "M";
21 exception
22 when OTHERS then
23 raise_application_error(-20001, SQLERRM||" on select gender");
24 end;
25
26 begin
27 select id into n_id from employee;
28 n_selected := sql%rowcount;
29 exception
30 when NO_DATA_FOUND then
31 n_selected := sql%rowcount;
32 DBMS_OUTPUT.PUT_LINE("Caught raised exception NO_DATA_FOUND");
33 when TOO_MANY_ROWS then
34 n_selected := sql%rowcount;
35 DBMS_OUTPUT.PUT_LINE("Caught raised exception TOO_MANY_ROWS");
36 when OTHERS then
37 raise_application_error(-20002, SQLERRM||" on select employee");
38 end;
39
40 DBMS_OUTPUT.PUT_LINE(to_char(n_selected)||" row(s) selected.");
41 end;
42 /
Caught raised exception NO_DATA_FOUND
0 row(s) selected.
PL/SQL procedure successfully completed.
SQL>
SQL> drop table gender;
Table dropped.
SQL> drop table employee;
Table dropped.
SQL>
Use a nested block to catch exceptions from singleton SELECT.
SQL> CREATE TABLE good_for (
2 product_id NUMBER,
3 good_for INTERVAL YEAR(2) TO MONTH NOT NULL
4 );
Table created.
SQL>
SQL> CREATE OR REPLACE FUNCTION get_expiration (p_product_id NUMBER)
2 RETURN DATE
3 AS
4 v_good_for good_for.good_for%type;
5 expiration_date DATE;
6 found_flag BOOLEAN;
7 BEGIN
8 BEGIN
9 SELECT gf.good_for INTO v_good_for
10 FROM good_for gf
11 WHERE gf.product_id = p_product_id;
12 found_flag := TRUE;
13 EXCEPTION
14 WHEN OTHERS THEN
15 found_flag := FALSE;
16 END;
17 IF found_flag THEN
18 expiration_date := TRUNC(SYSDATE) + v_good_for;
19 ELSE
20 expiration_date := null;
21 END IF;
22
23 RETURN expiration_date;
24 END;
25 /
Function created.
SQL>
SQL> INSERT INTO good_for (product_id, good_for)
2 VALUES (1, INTERVAL "1-4" YEAR TO MONTH);
1 row created.
SQL>
SQL> SELECT get_expiration(1) FROM DUAL;
GET_EXPIR
---------
24-NOV-09
SQL>
SQL>
SQL> drop table good_for;
Table dropped.
SQL>
SQL>
Using PRAGMA EXCEPTION_INIT
SQL>
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>
SQL>
SQL> DECLARE
2 e_MissingNull EXCEPTION;
3 PRAGMA EXCEPTION_INIT(e_MissingNull, -1400);
4 BEGIN
5 INSERT INTO Employee (id) VALUES (NULL);
6 EXCEPTION
7 WHEN e_MissingNull then
8 DBMS_OUTPUT.put_line("ORA-1400 occurred");
9 END;
10 /
ORA-1400 occurred
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>
Using SQLCODE for error code and SQLERRM for error message
SQL>
SQL> DECLARE
2 e_TooManyEmployee EXCEPTION; -- Exception to indicate an error condition
3 v_ErrorCode NUMBER; -- Variable to hold the error message code
4 v_ErrorText VARCHAR2(200); -- Variable to hold the error message text
5
6 BEGIN
7 RAISE e_TooManyEmployee;
8 EXCEPTION
9 WHEN e_TooManyEmployee THEN
10 DBMS_OUTPUT.put_line("e_TooManyEmployee");
11 DBMS_OUTPUT.put_line(v_ErrorCode);
12 DBMS_OUTPUT.put_line(v_ErrorText);
13
14 WHEN OTHERS THEN
15 v_ErrorCode := SQLCODE;
16 v_ErrorText := SUBSTR(SQLERRM, 1, 200); -- Note the use of SUBSTR here.
17 END;
18 /
e_TooManyEmployee
PL/SQL procedure successfully completed.
SQL>
SQL>