PL/SQL exception handling for Oracle database

Source: Internet
Author: User
Tags throw exception

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:

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:


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.


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.


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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.