Exception in Oracle

Exception in Oracle
1. Advantages of exceptions
　　
If no exception exists, check whether each command succeeds or fails in the program, 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 errors caused by an operation of PL/SQL code, 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_many_rows ora-01422-1422 the select into statement contains multiple returned records that meet the conditions
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 the raise statement
　　
3. Call the Stored Procedure 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