PL/SQL exception handling for Oracle database

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