Oracle PL/SQL Tutorial/PL SQL Programming/Exception
Содержание
- 1 Because OTHERS handles all exceptions, you must list it after any specific exceptions in your EXCEPTION block.
- 2 Catch "divide by zero" exception
- 3 Data not found exception
- 4 Declare variables in exception handler
- 5 DUP_VAL_ON_INDEX Exception
- 6 Exception Handling
- 7 Exceptions
- 8 INVALID_NUMBER Exception
- 9 OTHERS Exception
- 10 The NULL Statement
- 11 Types of more commonly used Exceptions
- 12 Understanding Different Exception Types
- 13 When the exception occurs, program control passes to the EXCEPTION block where the WHEN clause is examined for a matching exception.
- 14 ZERO_DIVIDE Exception
Because OTHERS handles all exceptions, you must list it after any specific exceptions in your EXCEPTION block.
SQL>
SQL> BEGIN
2 DBMS_OUTPUT.PUT_LINE(1 / 0);
3 EXCEPTION
4 WHEN OTHERS THEN
5 DBMS_OUTPUT.PUT_LINE("An exception occurred");
6 WHEN ZERO_DIVIDE THEN
7 DBMS_OUTPUT.PUT_LINE("Division by zero");
8 END;
9 /
WHEN OTHERS THEN
*
ERROR at line 4:
ORA-06550: line 4, column 4:
PLS-00370: OTHERS handler must be last among the exception handlers of a block
ORA-06550: line 0, column 0:
PL/SQL: Compilation unit analysis terminated
SQL>
Catch "divide by zero" exception
SQL> set serveroutput on
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>
Data not found exception
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.
Declare variables in exception handler
SQL> set serveroutput on
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 THEN
11 DECLARE
12 err_num NUMBER := SQLCODE;
13 err_msg VARCHAR2(512) := SQLERRM;
14 BEGIN
15 dbms_output.put_line("ORA Error Number " || err_num );
16 dbms_output.put_line("ORA Error message " || err_msg);
17 dbms_output.put_line(" Value of Num_a is " || Num_a);
18 dbms_output.put_line(" Value of Num_b is " || Num_b);
19 END;
20 END;
21 /
ORA Error Number -1476
ORA Error message ORA-01476: divisor is equal to zero
Value of Num_a is 6
Value of Num_b is 0
PL/SQL procedure successfully completed.
SQL>
SQL>
DUP_VAL_ON_INDEX Exception
The DUP_VAL_ON_INDEX exception is raised when an attempt is made to store duplicate values in a column that is constrained by a unique index.
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> BEGIN
2 INSERT INTO employee (id)VALUES(1);
3 EXCEPTION
4 WHEN DUP_VAL_ON_INDEX THEN
5 DBMS_OUTPUT.PUT_LINE("Duplicate value on an index");
6 END;
7 /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>
Exception Handling
The Syntax for Exceptions
EXCEPTION
WHEN OTHERS THEN
<statements>
statements is one or more statements that will be processed when the exception occurs.
You could always code a NULL statement if no action is to be taken.
Exceptions
In PL/SQL, the user can catch certain runtime errors.
Exceptions can be internally defined by Oracle or the user.
Exceptions are used to handle errors that occur in your PL/SQL code.
A PL/SQL block contains an EXCEPTION block to handle exception.
There are three types of exceptions:
- Predefined Oracle errors
- Undefined Oracle errors
- User-defined errors
The different parts of the exception.
- Declare the exception.
- Raise an exception.
- Handle the exception.
An exception has four attributes:
- Name provides a short description of the problem.
- Type identifies the area of the error.
- Exception Code gives a numeric representation of the exception.
- Error message provides additional information about the exception.
The predefined divide-by-zero exception has the following values for the attributes:
- Name = ZERO_DIVIDE
- Type = ORA (from the Oracle engine)
- Exception Code = C01476
- Error message = divisor is equal to zero
The following is the typical syntax of an exception-handling PL/SQL block.
exception
when exception_1 THEN
statements
when exception_2 THEN
statements
...
exception_1 and exception_2 are the names of the predefined exceptions.
statements is the PL/SQL code that will be executed if the exception name is satisfied.
INVALID_NUMBER Exception
The INVALID_NUMBER exception is raised when an attempt is made to convert an invalid character string into a number.
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> 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> BEGIN
2 INSERT INTO employee(salary) VALUES("123X");
3 EXCEPTION
4 WHEN INVALID_NUMBER THEN
5 DBMS_OUTPUT.PUT_LINE("Conversion of string to number failed");
6 END;
7 /
Conversion of string to number failed
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>
SQL>
SQL>
OTHERS Exception
You can use the OTHERS exception to handle all exceptions.
SQL> BEGIN
2 DBMS_OUTPUT.PUT_LINE(1 / 0);
3 EXCEPTION
4 WHEN OTHERS THEN
5 DBMS_OUTPUT.PUT_LINE("An exception occurred");
6 END;
7 /
An exception occurred
PL/SQL procedure successfully completed.
SQL>
SQL>
The NULL Statement
The NULL statement is simply a statement that does nothing.
Its format is simply
NULL;
Types of more commonly used Exceptions
The following are the more commonly used predefined exceptions:
no_data_found Singleton SELECT statement returned no data. too_many_rows Singleton SELECT statement returned more than one row of data. invalid_cursor Illegal cursor operation occurred. value_error Arithmetic, conversion, or truncation error occurred. invalid_number Conversion of a number to a character string failed. zero_divide Attempted to divide by zero. dup_val_on_index Attempted to insert a duplicate value into a column that has a unique index. cursor_already_open Attempted to open a cursor that was previously opened. not_logged_on A database call was made without being logged into Oracle. transaction_backed_out Usually raised when a remote portion of a transaction is rolled back. login_denied Login to Oracle failed. program_error If PL/SQL encounters an internal problem. storage_error If PL/SQL runs out of memory or if memory is corrupted. timeout_on_resource Timeout occurred while Oracle was waiting for a resource. others For all of the rest.
Understanding Different Exception Types
Identifying Exception Types
Error Code Prefix Indicates This Exception Type of Error ORA Core RDBMS errors PLS PL/SQL errors FRM Oracle Forms errors REP Oracle Reports errors
Oracle function SQLCODE returns the type and code of the most recently raised exception.
Exception names are usually associated with exceptions that you create yourself.
The exception message is a text string that describes the exception.
The function SQLERRM returns the text of the error message for the most recently raised exception.
When the exception occurs, program control passes to the EXCEPTION block where the WHEN clause is examined for a matching exception.
If no matching exception is found, the exception is propagated to the enclosing block.
SQL>
SQL> BEGIN
2 DBMS_OUTPUT.PUT_LINE(1 / 0);
3 END;
4 /
BEGIN
*
ERROR at line 1:
ORA-01476: divisor is equal to zero
ORA-06512: at line 2
ZERO_DIVIDE Exception
The ZERO_DIVIDE exception is raised when an attempt is made to divide a number by zero.
BEGIN
2 DBMS_OUTPUT.PUT_LINE(1 / 0);
3 EXCEPTION
4 WHEN ZERO_DIVIDE THEN
5 DBMS_OUTPUT.PUT_LINE("Division by zero");
6 END;
7 /
Division by zero
PL/SQL procedure successfully completed.
SQL>
SQL>