PL/SQL error handling stickers)

PL/SQL processing exceptions are different from otherProgramLanguage error management method. The PL/SQL exception handling mechanism is very similar to Ada, and there is a full-Inclusion Method for handling errors. When an error occurs, the program unconditionally redirects to the exception handling section, which requiresCodeIt should be very clean and separate the error handling part from the other part of the program. Oracle allows other exception condition types to be declared for extended error/exception handling. This extension makes PL/SQL exception handling very flexible.

An exception is thrown when a runtime error occurs. Errors During PL/SQL program compilation are not handled exceptions, and only exceptions during runtime can be handled. In PL/SQL programming, exception throws and handling are very important.
Throw an exception

An exception is thrown in three ways.

. Use the PL/SQL Runtime Engine

. Use raise statements

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

The following shows an order input system. When the inventory is smaller than the order, an inventory_too_low exception is thrown.

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 ';
Replenish_inventory (inventory_nbr =>
Inventory_rec.sku, min_amount => order_rec.qty-inventory_rec.qty );
End;
 

Replenish_inventory is a trigger.

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;
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;
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;

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

When an exception is handled, the propagation and resolution of the exception will be stopped. Sometimes you want the program to still execute some actions when an error occurs. To achieve this goal, you can place the actions you want to execute in the exception processor and then execute raise statements without parameters, the raise statement will throw an exception and allow it to continue propagation.

Declare
Order_too_old exception;
Begin
Raise order_too_old;
Exception
When order_too_old then
Declare
File_handle utl_file.file_type;
Begin
-- Open File
File_handle: = utl_file.fopen
(Location => '/ora01/APP/Oracle/admin/test/utlsir'
, Filename => 'error. Log'
. Open_mode => 'W ');
-- Write error Stack
Utl_file.put_line (filehandle,
Dbms_utility.format_error_stack );
-- Write the call stack
Utl_file.put_line (filehandle,
Dbms_utility.format_call_stack );
-- Close Error Log
Utl_file.fclose (file_handle );
Raise; -- Re-raise the exception
End
End

If a large value is output from format_xxx_stack, an error is displayed using dbms_output or utl_file, or the exception part of the call heap itself throws an exception, in general, these two heaps can return up to 2000 bytes, but utl_file.put_line is limited to 1000 bytes, while dbms_output.put_line is limited to 512 bytes. If the preceding code is used and this possibility is not allowed, an unhandled exception will be thrown in the exception processor.

The GOTO statement cannot be used to pass control from execution to exception or vice versa.

An error occurred while naming.

In the PL/SQL block exception section, only the named exceptions can be processed by the when substring. Oracle contains a series of named exceptions, which are declared in the standard package, these built-in exceptions are not described here. Interested readers can refer to the relevant information.
Common Exception Handling Methods

In addition to standard exceptions, you can declare your own named exceptions. For example, if a business rule is incorrect, or a custom exception is associated with a database number, you can even assign the database error number to a custom exception. This performance of PL/SQL greatly improves the ability to manage and handle exceptions.

Declare your own exceptions

If you want to declare your own exceptions, You can include the when clause in the exception processor. The declared exceptions have the same scope as declared variables. Exceptions declared on the outer block can be accessed by the block and its child blocks, but exceptions declared on the Child block cannot be handled by the parent block.

Example:

Begin
Declare
Insufficient_credite exception;
Begin
Rasise insufficient_credite;
Exception
When insufficient_credite then
-- Exception can be handled here
Extend_credite (cust_id );
End-end of nested Blocks
Exception
When insufficient_credite then
-- The exception cannot be handled here because it is out of the range.
End;

If the declared exception is the same as the built-in exception, then when you reference the exception, it will solve your exception instead of the built-in exception.

Name the database incorrectly

If you want to handle an exception, you must have a name for the exception. There are thousands of database errors, but less than 25 are built-in named exceptions. However, if you need to handle these unnamed exceptions, you can associate a name with an error code. To achieve this purpose, the Pragma exception_init statement is used.

Syntax:

Pragma exception_init (exception_name, error_number );

Before executing this statement, you must declare the Exception name.

Declare
Invald_table_name exception;
Pragma exception_init (invald_table_name,-942 );
Begin

Exception
When invald_table_name then
Utl_file.put_line (file_handle, 'user' | uid | 'Hit a bad table ');
End;

Another way to handle database errors is to use the built-in functions sqlcode and sqlerrm. These two functions are declared at the package level and sqlcode will return the current database error number, among these error numbers, the value of no_data_found is + 100, which is a negative number. Sqlerrm returns the error message of the text description. To obtain the sqlerrm and sqlcode returned by a custom exception, you need to use the raise_application_error function to mark the error number for the custom exception.

Mark custom error numbers

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 general information from unprocessed exceptions does not help identify the cause of the error. The raise_application_error function can be called in the execution part and exception part of the PL/SQL block, explicitly throws a naming exception with a special error code.

Raise_application_error (error_name, error_message [, {true | false}]);

The error number ranges from-20,999 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.

If product_not_found then
Raise_application_error (-20123, 'invald product Code' true );
End if;