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 ".