Plsql Exception Handling

Source: Internet
Author: User

The cursor has been browsed once, in order to improve the robustness of the Plsql program, first to write Plsql exception handling, that is, if the PLSQL code block error, the normal does not go to capture processing, will definitely stop execution, catch the exception and the corresponding processing, you can let the program continue to execute, An unknown error that occurs is called an exception.


1) Use pre-defined exceptions

2) Use of non-pre-defined exceptions

3) Use custom exceptions

Defining three exceptions is funny, predefined is Oracle's common error, non-predefined is to handle Oracle errors that the predefined cannot handle, and customization is to handle errors that are not related to Oracle.

Capturing and Handling exceptions


When Exception1 Then



When OTHERS Then


Remember when others is always in the last condition of exception handling, representing the above when you write all the times are not satisfied with the time to meet this others

"Pre-defined error"

A system exception provided by Plsql, which violates Oracle rules or system limitations, triggers an internal exception, presumably providing more than 20, with each of the predefined exceptions corresponding to an Oracle system error, such as No_data_found corresponding to a ORA-01403 error

This error is implicitly triggered when no data is obtained, too_many_rows, etc.

"Non-pre-defined error"

Non-predefined errors are used to handle Oracle errors unrelated to a predefined exception, and here is a case where the constraint is violated.

There are three steps to use:

1) Define the partial definition exception name, declare e_integrity exception;

2) Establish an association between exceptions and Oracle errors pragma exception_init (e_integrity,-2291);

3) Exception handling section captures and handles exceptions


E_integrity exception;

pragma exception_init (e_integrity,-2291);


Update emp set Deptno=%dno where empno=&eno;



When E_integrity Then

Dbms_output.put_line (' Not exists deptno ');

When others then

Dbms_output.put_line (Sqlcode);


"Custom Exceptions"

The biggest difference between custom exceptions is the trigger that needs to be displayed ,

Three steps to use:

1) Define part of the definition E_no_employee exception;

2) Perform a partial trigger if Sql%notfoud then raise E_no_employee;

3) Exception handling section captures and processes


E_integrity exception;

pragma exception_init (e_integrity,-2291);

E_no_employee exception;


Update emp set Deptno=&dno where empno=&eno;

If Sql%notfound Then

Raise E_no_employee;

End If;


When E_integrity Then

Dbms_output.put_line (' Deptno not exists ');

When E_no_employee Then

Dbms_output.put_line (' Empno not exists ');


The other is the exception function, which is often used when writing C to be sqlcode and SQLERRM, to get error numbers and error messages,

Dbms_output.put_line (Sqlcode);

Dbms_output.put_line (SQLERRM);

There are three types of warnings that are often used when compiling in a different way:

1) Server: If the alias of the parameter is the problem,

2) Performance: Performance issues, such as performance issues with type transformations

3) All: This keyword is used to check all warnings

Control warning message

Alter system set Plsql warnings= ' Enable:all ';

Alter session set plsql_warnings= ' Enable:performance ';

Alter procedure Hello Complie; it's like recompiling the hello process.

The key is to understand the three types of warning information, such as what the dead code needs to be checked, do not execute, but there should be code block execution performance problems, so when the check made performance,

The other point is that attributes and exceptions, such as Sql%notfound and No_data_found, are different from who produced them, and can be used at any time before you confuse

This article from "Yun Weibang" blog, declined reprint!

Plsql Exception Handling

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.