PL/SQL exception handling for Oracle database
Exceptions are abnormal events that occur during the course of a program's operation, usually caused by hardware problems or programming problems.
Even the best-written programs may encounter errors or unanticipated events during the PL/SQL programming process. A robust program should be able to properly handle various anomalies and recover from them as much as possible.
1. Exception Handling
Exception handling is used to handle unexpected events during normal execution. When a PL/SQL block generates an exception and does not indicate how to handle it, the program automatically terminates the entire program operation.
There are three types of exceptions during the PL/SQL Programming process:
1. Pre-defined exceptions
The handling of this exception does not need to be defined in the program, and is implicitly thrown when the PL/SQL program violates the Oracle rules or exceeds the system limits.
2. Non-predefined exceptions
Other standard Oracle errors. Handling this exception requires the user to define it in the program, which is then automatically raised by Oracle.
3. User-defined exceptions
During the execution of the program, it appears that the programmer considers the abnormal situation. Handling this exception requires the user to define it in the program and then explicitly raise it in the program.
Exception handling is usually placed at the back of the PL/SQL program, with the following syntax:
EXCEPTION when []| OTHERS} Then []...
2. Pre-defined exception handling
Common pre-defined exceptions:
Error number |
exception name |
description |
ORA-00001 |
dup_val_on_index |
Duplicate index value, violation of uniqueness limit, triggered when duplicate values are typed on a column corresponding to a unique index |
ORA-01001 |
invalid_cursor |
Attempting to use an invalid cursor |
ORA-01012 |
not_logged_on |
not connected to Oracle |
ORA-01017 |
login_denied |
Invalid username/password |
ORA-01403 |
no_data_found |
triggers when no data is found |
ORA-01422 |
too_many_rows |
return Multiple lines |
ORA-01722 |
invalid_number |
when converting to a number fails when triggered |
ORA-06511 |
cursor_already_open |
attempting to open a cursor that is already open |
ORA-06592 |
case_not_found |
Triggers when case conditions are not met |
See more pre-defined anomalies: http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/errors.htm#LNPLS00703
For the processing of predefined exceptions, simply refer to the exception-handling section of the PL/SQL block, directly referencing the corresponding exception case name, and complete the corresponding exception error handling.
Example 1:
DECLAREStock_price Number:= 9.73; Net_earnings Number:= 0; Pe_ratio Number;BEGINPe_ratio:=Stock_price/net_earnings; Dbms_output. Put_Line ('result of operation =' ||pe_ratio); EXCEPTION whenZero_divide ThenDbms_output. Put_Line ('/By Zero'); Pe_ratio:= NULL;END;
Operation Result:
/ by Zero
To avoid the exception of 0 exceptions, you can use the following example 2 to resolve:
Example 2:
DECLAREStock_price Number:= 9.73; Net_earnings Number:= 0; Pe_ratio Number;BEGINPe_ratio:= Casenet_earnings when 0 Then NULL ELSEStock_price/net_earningsEND;END;
Example 3:
DECLAREDefault_number Number:= 0;BEGIN INSERT intoTVALUES(To_number ('100.00','9g999')); EXCEPTION whenInvalid_number ThenDbms_output. Put_Line ('Replace illegal numbers with default values'); INSERT intoTVALUES(default_number);END;
Operation Result:
Replace illegal numbers with default values
3. Non-predefined exception handling
A non-predefined exception has an error number without a name and is handled by defining a name, binding to the error number, and capturing the error name. To handle this type of exception, you must first define a non-predefined Oracle exception.
Such as:
MYEXCP EXCEPTION;
Then use the Exception_init statement to connect to the standard Oracle error, such as:
PRAGMA Exception_init (MYEXCP,-02292);
Description: ORA-02292 is an error code that violates integrity constraints.
Example:
DECLAREMYEXCP EXCEPTION; PRAGMA Exception_init (MYEXCP,-02292); DNO Scott.emp.deptno%TYPE;BEGINDNO:= &Dept_no; DELETE fromScott.deptWHEREDeptno=DNO; EXCEPTION whenMyexcp Then DELETE fromScott.empWHEREDeptno=DNO; DELETE fromScott.deptWHEREDeptno=DNO; END;
4. User-defined exception handling
We can declare our own exceptions in the Declarations section of any PL/SQL anonymous block, subroutine, or package. A user-defined exception is explicitly triggered by using the raise statement.
The process for general user-defined exceptions is to catch and handle exceptions, such as defining exceptions, throwing exceptions.
Example:
DECLAREinvalidcategory EXCEPTION;--Defining ExceptionsCategoryVARCHAR2(Ten);BEGINCategory:= '&category'; IFCategory not inch('Accessories','top Cover','Spare Parts') ThenRAISE invalidcategory;--Throw Exception ELSEDbms_output. Put_Line ('the category you entered is'||category); END IF; EXCEPTION whenInvalidcategory Then --capturing and Handling exceptionsDbms_output. Put_Line ('the category is not recognized');END;
We can invoke the Raise_application_error procedure to raise and propagate application exceptions, which provides a way for the application to interact with Oracle.
The Raise_application_error procedure can be used to create user-defined error messages that can be used in the executable and exception handling sections, where the error number must be between –20000 and –20999, and the length of the error message can be up to 2048 bytes.
Raise_application_error process Syntax:
Raise_application_error (error_code, message[, {TRUE | FALSE}]);
If you specify True,pl/sql, the error message on the Error_code is added to the top of the stack. Specifies false,pl/sql to replace the Error_code error stack with the default value of FALSE.
Example 1:
DECLAREempno employees.employee_id%TYPE; No_such_row EXCEPTION;BEGINempno:= &empno; UPDATEEmployeesSETSalary=Salary+ - WHEREId=empno; IFSql%NOTFOUND ThenRAISE No_such_row; END IF; EXCEPTION whenNo_such_row ThenRaise_application_error (-20001,'no rows to modify');END;
Example 2:
BEGIN UPDATEEmpSETDeptno= the WHEREEmpno=1111; IFSql%NOTFOUND ThenRaise_application_error (-20001,'the employee does not exist! '); END IF; EXCEPTION whenOTHERS ThenDbms_output.put_line (SQLCODE||' -'||sqlerrm);END;
The Sqlcode is used to obtain the Oracle error number.
The SQLERRM is used to obtain the error message associated with it.
PL/SQL exception handling for Oracle database