Oracle Exception Handling

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.

Grammar:

Exception exception;

Where: Exception exception name

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

Grammar:

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