PL/SQL Exception Handling

Exceptions are classified into pre-defined exceptions and user-defined exceptions.
A predefined exception is a system-defined exception. Since they have been pre-defined in the standard package, these exceptions can be used directly in the program without being partially declared in the definition.
If a custom exception occurs, you must define a part of the Declaration before using it in the executable part. The error corresponding to a user-defined exception is not necessarily an oracle exception. For example, it may be a data error.
1. pre-defined exceptions
Common pre-defined exceptions...
-- The following block generates a NO_DATA_FOUND exception:

Code

Delcare
Type t_numbertabletype is table of number index by binary_integer;
V_numbertable t_numbertabletype;
V_tempvar number;
Begin
V_tempvar: = v_numbertable (1 );
End;

-- The following statement produces an INVALID_NUMBER exception, because '123a 'is not a legal salary value: Code

Insert into auths (author_code, name, birthdate, entry_date_time, salary)
Values ('a00022', 'King', to_date ('02-may-60'), to_date (04-Mar-90'), '123a ');
-- The following block will generate a value_error exception:
Delcare
V_tempvar varchar2 (3 );
Begin
V_tempvar: = 'abcd ';
End;

 

2. Abnormal statement

Code

Create or replace package globals
-- The object declared in this package can be referenced in any block. Note that this package does not have a package body.
E_userdefinedexception exception;
End global;

Because a pre-defined exception is only an exception associated with some oracle errors, if you want to handle oracle errors that do not correspond to a pre-defined exception, you must declare the user-defined exceptions for these oracle errors.
To declare such an exception, use the exception_init compilation command.
For example, the block definition section declares a user-defined exception e_toolarge that corresponds to the error "ORA-01401: inserted value too large for column. When the column value inserted into the table in the executable part of the block exceeds the specified column length, the e_toolarge exception occurs: Code declare
V_code auths. name % type;
E_toolarge exception;
Pragma exception_init (e_toolarge,-1401 );

NOTE: With exception_init, a user-defined exception can only be connected to an oracle error. In exception handling statements, sqlcode and sqlerrm will return the code and message text of this oracle error, rather than the user-defined message.

 

3. Exception generation
When an error corresponding to a predefined exception occurs, the predefined exception is automatically generated. A custom exception is usually generated by the RAISE statement (the custom exception declared by the exception_init compilation command can also be generated by the corresponding oracle error ).
Example 1: Use the raise statement in the following section to generate a user-defined exception e_toosmallsalary:

Code

Declare
-- Declare a user-defined exception
E_toosmallsalary exception;
V_currentsalary number (8, 2 );
V_smallsalary number (100) default;
Begin
Select salary into v_currentsalary from auths where author_code = 'a00002 ';
If v_currentsalary <v_smallsalary then
Raise e_toosmallsalary;
End if;
End;

When an exception occurs, the control is immediately handed over to the exception handling section of the block. If the block does not have an exception handling part, it is passed to the external block of the block. Once the control is handed over to the exception handling part, there is no way to return the executable part of the block.
For example 2, a pre-defined exception is usually generated when the corresponding error occurs. The following block generates a no_data_found exception. Code

Declare
V_name varchar2 (10 );
Begin
Select name into v_name from auths where author_code = 'b00006 ';
End;

Since there is no writer whose code number is "B00006", a "ORA-01403: not data found" error occurs, which corresponds to the no_data_found exception.
Example 3: a user-defined exception declared by the exception_init compilation command is connected to an oracle error. Therefore, such a user-defined exception is also generated when an oracle error occurs.
A user-defined exception e_toolarge has been declared in the previous section (using exception_init to compile the command Declaration ). When a column value is inserted into a table that exceeds the length specified by the column, this exception is automatically generated: Code

Begin
Insert into auths (author_code, name, birthdate, entry_date_time)
Values ('a000001 ', 'wang', 'to _ date ('11-August-50'), to_date ('12-August-97 '));
End;

Because the input writer code value 'a00000' exceeds the column length, the error "ORA-01401: inserted value too large for column" is generated, which corresponds to the user-defined exception toolarge, at the same time, the control is transferred to the call environment outside the block.
Generally, custom exceptions are generated only after they are declared. However, if we use the raise_application_error function, we can directly generate exceptions and define custom error messages for exceptions. After the raise_application_error function is executed, the control is transferred to the call situation outside the block.
Raise_application_error (error_number, error_message [, keep_errors]);
Error_number is an error number between-20000 and-20999. error_message is the error message text that is connected to the error. It cannot exceed 512 characters. Keep_errors is a boolean value. Is an optional parameter. If it is true, this new error will be added after the generated error list. If it is false, this new error will replace the current error list.
For example:
Raise_application_error (-20001, 'No code is' | p_author_code | 'writer exist ');

 

4. Exception Handling
The exception handling part contains the exception handling statement. When an exception occurs, the Exception Processing statement is executed.

Code

Declare
E_toosmallsalary exception;
V_currentsalary number (8, 2 );
V_smallsalary number (100) default;
Begin
Select salary into v_currentsalary from auths where author_code = 'a00002 ';
If v_currentsalary <v_smallsalary then
Raise e_toosmallsalary;
End if;
Exception
When e_toosmallsalary then
Update auths set salary = 500 where author_code = 'a00002 ';
End;

One exception processing statement can handle multiple exceptions. You only need to add multiple exception names separated by or in the when clause.
If the exception in the block is not processed, the block will return the program that calls it with the unhandled exception, which leads to an error in the program that calls it. If an exception occurs during the stored procedure, the OUT parameter of the stored procedure will not return the value.
To avoid problems caused by unhandled exceptions, we recommend that you use the others clause at the outermost layer of the block to handle all unhandled exceptions in the block. In this way, all errors can be detected and handled.
(1) Handling exceptions in the executable part
(2) handling exceptions in the definition section
If an assignment statement in the definition part produces an exception, even if the exception handling part of the current block has a processing statement that handles the exception, it will not be executed, instead, it is immediately passed to the external block. Processed by external blocks.
(3) Handling exceptions
An exception can also be generated in an exception handling statement. This exception can be generated using a raise statement or a running error. In both cases, the exception is immediately passed out of the block.
(4) sqlcode and sqlerrm Functions
Because the others clause handles exceptions not handled by the when clause, the exceptions processed in the others clause are unknown. We can use sqlcode and sqlerrm functions to determine the error code and information corresponding to the exception.
Exception type sqlcode sqlerrm
Oracle error corresponding to abnormal negative oracle Error
No_data_found + 100 no data found
Custom exception + 1 user-defined exception
No exception occurred 0 ora-0000: normal, successful completion
Note: If you use the prediction_init pre-compilation command to declare a custom exception that is connected to an oracle error, sqlcode and sqlerrm return the corresponding oracle error code and corresponding error information, it does not return "+ 1" and "user-defined exception ".
For example, the following is a PL/SQL block with a complete others Exception Handling statement: Code

Declare
E_toosmallsalary exception;
V_currentsalary number (8, 2 );
V_smallsalary number (100) default;
V_errorcode number; -- get the variable of the error message code.
V_errortext varchar2 (200); -- get the variable of the error message text.
Begin
Select salary into v_currentsalary from auths where author_code = 'a00002 ';
If v_currentsalary <v_smallsalary then
Raise e_toosmallsalary;
End if;
Exception
When e_toosmallsalary then
Delete auths where author_code = 'a00002 ';
When others then
V_errorcode: = sqlcode;
V_errortext: = substr (sqlerrm, 1,200 );
Dbms_output.put_line (v_errorcode );
Dbms_output.put_line (v_errortext );
End;

Note: If you want to use sqlcode and sqlerrm in SQL statements, you must first assign their values to local variables and then use these local variables in SQL statements, these functions are procedural and cannot be directly used in SQL statements.