Comprehensive Analysis of Oracle Developer's Exception Handling Mechanism

Oracle Developer is famous for its rapid data processing and development, and its exception handling mechanism is also relatively complete.

1. Advantages of exceptions

If no exception occursProgramCheck whether each command succeeds or fails, as shown in figure

Begin

Select...

-- Check for 'no data found 'Error

Select...

-- Check for 'no data found 'Error

Select...

-- Check for 'no data found 'Error

The disadvantage of this implementation method is that error processing is not separated from normal processing, with poor readability and exception usage, which facilitates error handling. Besides, exception handling programs are separated from normal transaction logic, improving readability, for example

Begin

Select...

Select...

Select...

...

Exception

When no_data_found then -- catches all 'no data found' errors

2. Exception Classification

There are two types of exceptions: Internal exceptions and custom exceptions. Internal exceptions are Oracle errors returned to PL/SQL blocks during execution or Oracle errors returned by PL/SQLCodeErrors Caused by an operation, such as Zero Divisor or memory overflow. Custom exceptions are displayed and defined by developers. Information is transmitted in PL/SQL blocks to control application error handling.

Every time PL/SQL violates Oracle principles or goes beyond system dependencies, internal exceptions are implicitly generated. Because each Oracle error has a number and the PL/SQL exception is handled by the name, Oracle provides a predefined internal exception. If the select into statement does not return a row, the Oracle exception no_data_found is generated. For pre-defined exceptions, the most common exceptions are listed as follows:

Exception Oracle error sqlcode Value Condition

No_data_found ora-01403 + 100 select into Statement no matching record returned

Too_mang_rows ora-01422-1422 select into statement multiple records that meet the condition are returned

Dup_val_on_index ora-00001-1 for a column in a database table, this column has been restricted to a unique index, and the program tries to store two duplicate values

Value_error ora-06502-6502 this exception occurs when converting character types, truncation, or length constraints, such as a character assigned to a variable that declares a shorter length than the character, this exception is thrown.

Storage_error ora-06500-6500 memory overflow

Zero_divide ora-01476-1476 divisor 0

Case_not_found ora-06592-6530 for the select case statement, there are no matching conditions and no else statement captures other conditions

Cursor_already_open ora-06511-6511 attempts to open an opened cursor

Timeout_on_resource ora-00051-51 the system is waiting for a resource, time-out

To handle unnamed internal exceptions, you must use the others exception processor or pragma exception_init. Pragma is controlled by the compiler or comments to the compiler. Pragma is processed during compilation rather than during runtime. Exception_init tells the compiler to combine the Exception name with the Oracle error code, so that any internal exception can be referenced by the name, and an appropriate exception processor can be written by name for the exception.

The syntax for using prediction_init in subprograms is as follows:

Pragma exception_init (exception_name,-oracle_error_number );

In this syntax, the exception name is declared as an exception. The following example shows its usage:

Declare

Deadlock_detected exception;

Pragma exception_init (deadlock_detected,-60 );

Begin

... -- Some operation that causes an ORA-00060 Error

Exception

When deadlock_detected then

-- Handle the error

End;

For user-defined exceptions, only partial declaration exceptions in PL/SQL blocks can be declared. The Exception name is introduced by the exception Keyword:

Reserved_loaned exception

After an exception is generated, the control is passed to the exception section of the subroutine, and the exception is switched to the respective exception control block. The following structure must be used in the code to handle the error:

Exception

When exception1 then

Sequence of statements;

When exception2 then

Sequence of statements;

When others then

3. Thrown exceptions

An exception is thrown in three ways.

◆ 1. Use the PL/SQL Runtime Engine

◆ 2. Use raise statements

◆ 3. Call the stored procedure of raise_application_error

When an error occurs during database or PL/SQL running, an exception is automatically thrown by the PL/SQL runtime engine. An exception can also be thrown through the raise statement.

Raise prediction_name;

Explicit throws are a habit of programmers to handle declared exceptions, but raise is not limited to declared exceptions. It can throw any exceptions. For example, if you want to use timeout_on_resource error to detect a new runtime exception processor, you only need to use the following statement in the program:

Raise timeout_on_resouce;

For example, in the following order Input example, if the order is smaller than the inventory quantity, an exception is thrown, and the exception is caught to handle the exception.

Declare

Inventory_too_low exception;

--- Other statements

Begin

If order_rec.qty> inventory_rec.qty then

Raise inventory_too_low;

End if

Exception

When inventory_too_low then

Order_rec.staus: = 'backordered ';

End;

 

The raise_application_error built-in function is used to throw an exception and assign an error number and error message to the exception. The default error code for custom exceptions is + 1, and the default information is user_defined_exception. The raise_application_error function can be called in the execution part and exception part of the PL/SQL program block, explicitly throwing a naming exception with a special error code. Raise_application_error (error_number, message [, true, false])

The error number ranges from-20,000 to-20,999. The error message is a text string, which consists of up to 2048 bytes. True and false indicate whether to add (true) to the error stack or overwrite (false ). The default value is false.

The following code is used:

If product_not_found then

Raise_application_error (-20123, 'invald product Code' true );

End if;

4. Exception Handling

The exception section of the PL/SQL block contains the code for processing errors. When an exception is thrown, an exception trap automatically occurs, and the program control is removed from the execution part and transferred to the exception part, once the program enters the exception part, it cannot return to the execution part of the same part. The following is the general syntax of the exception section:

Exception

When exception_name then

Code for handing exception_name

[When another_exception then

Code for handing another_exception]

[When others then

Code for handing any other exception.]

The user must design the exception handling code for each exception in the independent when substring, And the when others substring must be placed at the end as the default processor to handle exceptions that are not explicitly processed. When an exception occurs, control the switch to the exception section. Oracle searches for the corresponding when .. the then statement captures exceptions. The code after the then is executed. If the error Trap Code only exits the corresponding nested block, the program continues to execute the statement after the internal block end. If no exception trap is found, the system executes when others. In the exception section, there is no limit on the number of when substrings.

Exception

When inventory_too_low then

Order_rec.staus: = 'backordered ';

Replenish_inventory (inventory_nbr =>

Inventory_rec.sku, min_amount => order_rec.qty-inventory_rec.qty );

When discontinued_item then

-- Code for discontinued_item Processing

When zero_divide then

-- Code for zero_divide

When others then

-- Code for any other exception

End;

When an exception is thrown, the control is unconditionally transferred to the exception part, which means that the control cannot return to the exception location. After the exception is handled and resolved, control the next statement returned to the execution part of the previous layer.

Begin

Declare

Bad_credit exception;

Begin

Raise bad_credit;

-- If an exception occurs, control the redirection;

Exception

When bad_credit then

Dbms_output.put_line ('bad _ credentials ');

End;

-- After bad_credit exception handling, the control goes here

Exception

When others then

-- Control will not go from bad_credit exception to here

-- Because bad_credit has been processed

End;

When an exception occurs and there is no such exception processor in the block, the control will go to or spread to the exception handling part of the previous block.

Begin

Declare --- internal block start

Bad_credit exception;

Begin

Raise bad_credit;

-- If an exception occurs, control the redirection;

Exception

When zero_divide then -- cannot handle bad_credite exceptions

Dbms_output.put_line ('divide by zero error ');

End -- end the internal Block

-- The control cannot be reached here because the exception is not resolved;

-- Exception

Exception

When others then

-- Since bad_credit is not resolved, control will go here

End;

5. Abnormal Propagation

Exceptions that are not handled will be propagated along the suspicious exception calling program. When an exception is handled and resolved, or when the exception reaches the outermost layer of the program, the propagation stops. The exception thrown in the declaration part is controlled to the exception part of the previous layer.

Begin

Executable statements

Begin

Today Date: = 'syadate'; -- errror

Begin -- Internal block start

Dbms_output.put_line ('This line will not execute ');

Exception

When others then

-- Exceptions will not be handled here

End; -- Internal block end

Exception

When others then

Exception Handling

End

The exception thrown by the execution part is first transmitted to the exception part of the same part. If the exception part of the same part is not processed by the processor, the exception will be transmitted to the exception section of the previous layer until the outermost layer.

The exception thrown in the exception section is controlled to the exception section in the previous layer.

In addition, the error reporting functions sqlcode and sqlerrm are particularly useful in others processors because they return Oracle error codes and messages. For example:

Declare

Err_num number;

Err_msg varchar2 (100 );

Begin

...

Exception

When others then

Err_num: = sqlcode;

Err_msg: = substr (sqlerrm, 1,100 );

Insert into errors values (err_num, err_msg );

End;

 

~~~~ There are three knowledge points on Exception Handling!

 

1. prediction_int compilation instructions

This function associates a naming exception with a specific Oracle error.

It is used to capture a specific exception error, rather than processing it through others.

Syntax: progma exception_init (exception_name, oracle_error_number)

 

SQL>

SQL> declare

2 expa exception;

3 Pragma exception_init (expa,-6502 );

4 VN number (1 );

5 begin

6 Vn: = 24;

7 exception

8 When expa then

9 dbms_output.put_line ('wrong number or value: the value is too accurate ');

10 when others then

11 dbms_output.put_line (sqlcode );

12 dbms_output.put_line (substr (sqlerrm, 1,100 ));

13 end;

14/

Incorrect number or value: the value is too accurate.

 

PL/SQL procedure successfully completed

 

SQL>

 

2. raise_application_error

Function uses custom error messages

Syntax: raise_application_error (error_number, error_message, [keep_errors])

Error_number: value between-20 000 and-20 999

Error_message: must be less than 512 characters

Keep_errors: Boolean value. if it is true, the new error will be added to the existing Error List (if it already exists). If it is false (default), the new error will replace the current error list.

 

SQL> begin

2 update t set a = 'test' where a = '000000 ';

3 If SQL % notfound then

4 raise_application_error (-20001, 'Are you stupid? We know that such data is still being updated! ');

5 end if;

6 exception

7 when others then

8 dbms_output.put_line (substr (sqlerrm, 1,100 ));

9 end;

10/

ORA-20001: You stupid? We know that such data is still being updated!

 

PL/SQL procedure successfully completed

 

SQL>

 

3. Exception Propagation

 

An exception occurred in the executable part of.

1) if the current statement block has a handler for this exception, it will be executed and the control will be handed over to the outer statement block.

 

The current statement block contains the exception handler:

SQL> declare

2 expa exception;

3 expb exception;

4 begin

5 begin

6 raise expa;

7 exception

8 When expa then

9 dbms_output.put_line ('exception A generation ');

10 end;

11 exception

12 when expb then

13 dbms_output.put_line ('exception B generation ');

14 end;

15/

Exception A Generation

 

PL/SQL procedure successfully completed

 

SQL>

2) if the current statement does not have a handler for this exception, the exception is generated in the outer statement block to spread the exception, and then processed by the outer exception handler in step 1. if the exception handler does not exist in the outer layer, the exception is propagated to the calling environment.

 

The current statement block does not have a handler for this exception. The outer statement block generates this exception and processes it.

SQL> declare

2 expa exception;

3 expb exception;

4 begin

5 begin

6 raise expb;

7 exception

8 When expa then

9 dbms_output.put_line ('exception A generation ');

10 end;

11 exception

12 when expb then

13 dbms_output.put_line ('exception B generation ');

14 end;

15/

Exception B generation

 

PL/SQL procedure successfully completed

 

SQL>

 

The current statement block does not have a handler for this exception. The outer statement block generates this exception and does not have the handler. The exception is propagated to the calling environment.

SQL> declare

2 expa exception;

3 expb exception;

4 begin

5 begin

6 raise expb;

7 exception

8 When expa then

9 dbms_output.put_line ('exception A generation ');

10 end;

11 exception

12 when expa then

13 dbms_output.put_line ('exception A generation ');

14 end;

15/

 

Declare

Expa exception;

Expb exception;

Begin

Begin

Raise expb;

Exception

When expa then

Dbms_output.put_line ('exception A generation ');

End;

Exception

When expa then

Dbms_output.put_line ('exception A generation ');

End;

 

ORA-06510: PL/SQL: User-defined exception events that cannot be processed

ORA-06512: In line 6

 

SQL>

 

Exceptions in Part B Declaration

If an exception occurs when a value assignment is declared, the exception is immediately propagated to the outer statement block and then handled according to the "A executable part of the exception" rule.

 

SQL> begin

2 declare

3 VN number (1): = 25;

4 begin

5 NULL;

6 exception

7 when others then

8 dbms_output.put_line ('exceptions are handled at the internal layer! ');

9 end;

10 exception

11 when others then

12 dbms_output.put_line ('exception is handled on the outer layer! ');

13 end;

14/

The exception is handled on the outer layer!

 

PL/SQL procedure successfully completed

 

SQL>

 

C exceptions

Exceptions may also occur in the exception processor, such as raise or running errors. The exception is immediately propagated to the outer layer. "exceptions in Part B Declaration"

 

Example 1

SQL>

SQL> begin

2 declare

3 expa exception;

4 expb exception;

5 begin

6 raise expa;

7 exception

8 When expa then

9 raise expb;

10 when expb then

11 -- although there is an expb Exception Processing statement, the exception will be immediately propagated to the outer layer.

12 dbms_output.put_line ('internal layer caught exception B ');

13 end;

14 exception

15 when others then

16 dbms_output.put_line ('outer layer caught exception B ');

17 end;

18/

Outer layer caught exception B

 

PL/SQL procedure successfully completed

 

SQL>

 

Example 2

SQL> DESC T;

Name type

-------------

ID number (1)

 

SQL> begin

2 declare

3 expa exception;

4 begin

5 raise expa;

6 exception

7 when expa then

8 insert into T values (12 );

9 when others then

10 dbms_output.put_line ('internal layer caught exception ');

11 end;

12 exception

13 when others then

14 dbms_output.put_line ('outer layer caught exception ');

15 end;

16/

Exceptions caught by the outer layer

 

PL/SQL procedure successfully completed

SQL>

 

19:10:25 I. In PLSQL, Oracle's built-in exceptions are as follows:

Exception Oracle error sqlcode Value

Access_1__null ORA-06530-6530

Case_not_found ORA-06592-6592

Collection_is_null ORA-06531-6531

Cursor_already_open ORA-06511-6511

Dup_val_on_index ORA-00001-1

Invalid_cursor ORA-01001-1001

Invalid_number ORA-01722-1722

Login_denied ORA-01017-1017

No_data_found: ORA-01403 + 100

Not_logged_on ORA-01012-1012

Program_error ORA-06501-6501

Rowtype_mismatch ORA-06504-6504

Self_is_null ORA-30625-30625

Storage_error ORA-06500-6500

Subscript_beyond_count ORA-06533-6533

Subscript_outside_limit ORA-06532-6532

Sys_invalid_rowid ORA-01410-1410

Timeout_on_resource ORA-00051-51

Too_many_rows ORA-01422-1422

Value_error ORA-06502-6502

Zero_divide ORA-01476-1476

 

2007-5-7 21:45:42 Oracle sqlcode/sqlerrm

The built-in Oracle functions sqlcode and sqlerrm are especially used in the others processor to return the Oracle error code and error messages respectively.

The others processor should be the final exception processor in the exception processing block because it is used to capture all Oracle exceptions except for Exception Processing by other exception processors, so using an others processor at the outermost layer of the program ensures that all errors are detected.

 

In an internal exception, sqlcode returns the Oracle error serial number, while sqlerrm returns the corresponding error message. The error message first displays the error code. Sqlcode returns a negative number, unless the Oracle error is "ORA-01403: No data found" (translated: ORA-01403: No data found), when the Oracle error is "ORA-01403: No data found, the corresponding sqlcode is + 100. For user-defined exceptions, sqlcode returns + 1, while sqlerrm returns user-defined exception.

 

An Oracle error message can contain up to 512 bytes of error code. If no exception is triggered, sqlcode returns 0, sqlerrm returns "ORA-0000: Normal, successful completion ".