Oracle Exception Handling

Source: Internet
Author: User
Tags define exception dname terminates

1. What is an exception a warning or error condition in PL/SQL can be referred to as an exception. Includes compile-time errors (PLS) and run-time errors (ORA). An exception usually contains an error code and error text, indicating the number of the exception and the specific error message, respectively. exception Handling (EXCEPTION) is used to handle unexpected events during normal execution, program block exceptions handle pre-defined errors and custom errors, and the program automatically terminates the entire program as soon as the PL/SQL block generates an exception and does not indicate what to do with it. 2. Classification of anomalies

1. Pre-defined (predefined) errors

There are approximately 24 pre-defined exception cases for Oracle. The handling of this exception does not have to be defined in the program and is automatically raised by Oracle.

2. Non-pre-defined (predefined) error

That is, 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 (user_define) error

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.

3. Abnormal structure

EXCEPTION whenException1[OR exception2 ...]  Thenstatement1;      Statement2; . . .    [When Exception3 [OR exception4 ...]  Thenstatement1;      Statement2;    . . .] [When OTHERS and then statement1;      Statement2; . . .] --the When clause in the exception section has no quantity limit--When OTHERS is the last clause--the exception handling section starts with the keyword exception--when the exception is thrown, control unconditionally goes to the Exception handling section--only one exception handling can be performed before leaving the block

4. Exception type

4.1 Pre-defined exceptions

Predefined exceptions are pre-defined by Oracle for common errors and do not need to be explicitly declared.

Intercept an Oracle server predefined error by referencing the wrong standard name in the appropriate exception-handling routine.

DECLARETestvarchar(Ten);BEGIN    SELECTDname intoTest fromDEPTWHEREDEPTNO=' One'; Dbms_output. Put_Line (test); EXCEPTION whenNo_data_found ThenDbms_output. Put_Line ('No qualifying data found');  whenToo_many_rows ThenDbms_output. Put_Line ('too many rows of data returned');  whenOTHERS ThenDbms_output. Put_Line ('Eorror:'||SQLCODE||'-'||sqlerrm);END;

4.2 Non-pre-defined exceptions

1. Declare the exception name in the Declarations section.


Exception exception;

Where: Exception exception name

2. Use the Pragmaexception_init statement to associate the exception handling name with the Oracle error code.


PRAGMA Exception_init (EXCEPTION, error_number);

Where: Exception previously declared exception name

Error_number Standard Oracle Error codes

3. Reference the declared exception in the appropriate exception-handling routine.

The keyword PRAGMA (pseudo-directive pseudoinstructions) indicates that the statement is a compilation instruction and does not process the statement when the PL/SQL block is executed. In the PL/SQL block, a compile instruction Exception_init tells the compiler to associate the name of an exception handler with an Oracle error code.

DECLAREe_emp_cons EXCEPTION; PRAGMA Exception_init (e_emp_cons,-00001); BEGIN      INSERT  intoEMPSELECT *  fromEMP; EXCEPTION whenE_emp_cons ThenDbms_output.put_line ('violation of uniqueness constraints'); END;

4.3 custom exceptions

Declared in the Declarations section of the PL/SQL block

Use the Raise statement to publish explicitly

DECLAREnamevarchar(Ten);   Ex EXCEPTION; --Define Exception exBEGIN    SELECTDname intoName fromDEPTWHEREDEPTNO='Ten'; Dbms_output.  Put_Line (name); IFNAME<>'HR'  ThenRAISE ex; --triggering an exception    END IF; EXCEPTION whenEx ThenDbms_output. Put_Line ('department number 10th is not HR .');--Handling ExceptionsEND;

4. Abnormal transmission

When a child block handles an exception itself, it terminates normally, and the control is immediately passed to the outer block after the end statement of the child block.

However, if PL/SQL has an exception, but there is no processor for the exception in the current block, it will look for a processor in the outer block, and if all the external blocks cannot handle the exception, an unhandled exception will occur in the host environment.

When an exception is propagated to an external block, the pending execution code in the current block is no longer executed.

The advantage of this approach is that the inner block only handles its own unique errors, leaving the general exception handling to the external block.

Oracle Exception Handling

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.