Oracle Exception (-)

I. Overview
Exceptions fall into three main categories: predefined exceptions, non-predefined exceptions, custom exceptions
The processing method is divided into: Direct throw exception, internal block handling exception, cursor handling exception

Predefined exception: An exception defined by PL/SQL. Because they are pre-defined in the standard package, these predefined exceptions can be used directly in the program without having to define partial declarations.

Non-predefined exception: An Oracle error that is used to handle a predefined exception that cannot be handled.

Custom exceptions: User-defined exceptions that need to be defined in a partial declaration before they can be used in the executable section. The error that corresponds to a user-defined exception is not necessarily an oracle error, such as it may be a data error.

Of the three exceptions, both predefined and non-predefined exceptions are related to Oracle errors, and are implicitly automatically thrown by Oracle,
A custom exception is not associated with an Oracle error, and the exception defined by the developer for a particular case requires an explicit throw (raise).


Second, pre-defined anomalies

1. Types of pre-defined anomalies
1.1, Access_into_null
Description: Corresponds to ORA-06530, when the Create object type is applied, before the object property is referenced, if the object is not initialized,
Assign a value directly to an object property, this exception is set.
Example: Create type Emp_type As Object (name Varchar2 (ten), Sal number (6,2));
Declare
EMP Emp_type;
Begin
Emp.name:= ' Scott ';
exception
When Access_into_null dbms_output.put_line (' uninitialized ');
End

1.2, Case_not_found
Description: When writing a case statement that corresponds to ORA-06592, this exception is set if the When clause does not contain the required conditional branch and does not contain an ELSE clause.

1.3, Collection_is_null
Description: For ORA-06531, this exception is set if the collection element is not initialized when the collection element (nested table or Varray) is assigned a value.
Example: Declare
Type Ename_table_type is table of Emp.ename%type;
Ename_table Ename_table_type;
Begin
Select Ename to Ename_table (2) from EMP where empno=&no;
Dbms_output.put_line (Name_table (2));
exception
When Collection_is_null and then dbms_output.put_ling (' uninitialized ');
End

1.4, Cursor_already_open
Description: ORA-06511, which triggers this exception when a cursor that is already open is opened.

1.5, Dup_val_on_index
Description: For ORA-00001, this exception is triggered when a duplicate value is typed on a column that corresponds to a unique index.

1.6, Invalid_cursor
Description: Corresponds to ORA-01001, which triggers this exception when an attempt is made to perform an operation on an illegal cursor. For example, a cursor that has never been opened extracts data or closes an open cursor.

1.7, Invalid_number
Description: Corresponding to ORA-01722, this exception is implicitly triggered when an inline SQL statement cannot effectively convert a character to a number.

1.8, No_data_found
Description: This exception is triggered when the execution of SELCT into does not return a row, or if the index table is not initialized with an element that corresponds to ORA-01403.

1.9, Too_many_rows
Description: This exception is triggered when the execution of SELECT into returns more than one row, corresponding to ORA-01422.

1.10, Zero_divide
Description: This exception is triggered when the divisor is 0 o'clock, corresponding to ORA-01476.

1.11, Subscript_beyond_count
Description: Corresponding to ORA-06533, this exception is triggered when a nested table or varray element is used, if the element subscript is out of range.
Example: Declare
Type Emp_array_type is Varray of VARCHAR2 (10);
Emp_array Emp_array_type;
Begin
Emp_array:=emp_array_type (' Scott ', ' Mary ');
Dbms_output.put_line (Emp_array (3));
exception
When Subscript_beyond_count and then Dbms_output.put_line (' Out of Range ');
End

1.12, SUBSCRIPT_OUTSIDE_LIMT
Description: Corresponding to ORA-06532, this exception is triggered when a nested table or varray element is used, and if the subscript is a negative number.

1.13, Value_error
Description: For ORA-06502, this exception is triggered when an assignment operation is performed, if the variable length is not sufficient to accommodate the actual data.

1.14, login_denied
Description: For ORA-01017, this exception is triggered when an Oracle database is connected, if the user name or password is incorrect.

1.15, not_logged_on
Description: For ORA-01012, this exception is triggered if a PL/SQL block is executed without a connection to the database.

1.16, Program_error
Description: For ORA-06501, if this error occurs, there is a PL/SQL internal issue that may require the user to reinstall the data dictionary and the PL/SQL System package.

1.17, Rowtype_mismatch
Description: For ORA-06504, this exception is triggered when an assignment operation is performed, if the return type of the host cursor variable and the PL/SQL cursor variable is incompatible.

1.18, Self_is_null
Description: For ORA-30625, this exception is triggered when a member method is called on a null instance when the object type is used.

1.19, Storage_Error
Description: This exception is triggered when the Ora-06500,pl/sql block is running, if it is out of memory space or memory is corrupted.

1.20, Sys_invalid_rowid
Description: Corresponding to ORA-01410, this exception is triggered when a character is converted to ROWID, if an invalid string is used.

1.21, Timeout_on_resource
Description: For ORA-00051, this exception is triggered if a time-out error occurs while Oracle waits for a resource.

2. Handling of pre-defined anomalies

Description: Here are two common exceptions no_data_found and too_many_rows, both of which are triggered by a SELECT INTO statement.
Example:
Declare
V_CNT Number: = 800;
V_name Emp.ename%type;
Begin
Select Ename to V_name from EMP where sal=v_cnt;
Dbms_output.put_line (' Name: ' | | v_name);
exception
When No_data_found then--throws an exception directly
Dbms_output.put_line (' Employees who do not have the wage value ');
When Too_many_rows Then
Dbms_output.put_line (' There are multiple employees with this wage ');
When others then
Rollback
Dbms_output.put_line (' exception rollback exit ');
End

2.1, No_data_found Abnormal
Cause: When assigning a value to a variable, the result of the query is empty.
Note: As the above example shows, once an exception is thrown directly, the process is interrupted. No_data_found this anomaly, not serious enough to let the program to the point of interruption, can be completely handed over to the program for processing.

2.1.1, using internal independent block processing
Note: This is a good way to do this, and it will not cause the program to break because of this exception.
For example:
Declare
v_cnt:=800;
V_name Emp.ename%type;
Begin
Begin
Select Ename to V_name from EMP where sal=v_cnt;
exception
When No_data_found Then
V_name:= ";
End
Dbms_output.put_line (' Name: ' | | v_name);
exception
When Too_many_rows Then
Dbms_output.put_line (' There are multiple employees with this wage ');
When others then
Rollback
Dbms_output.put_line (' exception rollback exit ');
End

2.1.2, using cursor handling
Description: Cursor operations can completely avoid no_data_found exceptions.
For example:
Declare
v_cnt:=800;
V_name Emp.ename%type;
Cursor C_cursor is a select ename from emp where sal=v_cnt;
Begin
Open c_cursor;
Fetch c_cursor into v_name;
Close c_cursor;
Dbms_output.put_line (' Name: ' | | v_name);
exception
When Too_many_rows Then
Dbms_output.put_line (' There are multiple employees with this wage ');
When others then
Rollback
Dbms_output.put_line (' exception rollback exit ');
End

2.2, Too_many_rows Abnormal
Cause: When assigning a value to a variable, the query results in multiple records or a SELECT INTO statement with the same variable name as the table name.
Note: Returning multiple records if they are acceptable must be handled with cursors, and if unacceptable, internal fast processing must be used.

(1), return multiple records if it is acceptable, then take a random one, with a cursor processing.

(2), return multiple records if it is unacceptable, you must catch the exception and handle it with an internal block.
Example:
Declare
v_cnt:=800;
V_name Emp.ename%type;
Begin
Begin
Select Ename to V_name from EMP where sal=v_cnt;
exception
When No_data_found Then
V_name:= ";
When Too_many_rows Then
V_name:= ";
Dbms_output.put_line (' There are multiple employees with this wage ');
End
Dbms_output.put_line (' Name: ' | | v_name);
exception
When others then
Rollback
Dbms_output.put_line (' exception rollback exit ');
End


Third, non-predefined exceptions
Description: A non-predefined exception was used to handle Oracle errors unrelated to 21 predefined exceptions.
Because a predefined exception is just an exception that is connected to a subset of Oracle errors, if you are dealing with an Oracle error that does not correspond to a predefined exception,
You will need to declare the corresponding non-predefined exceptions for these Oracle errors. Declaring such an exception requires the use of Exception_init compilation directives.

The exception_init compiler directive is defined as follows:
pragma exception_init (exception_name,oracle_error_number);
Exception_name is a pre-declared exception name, Oracle_error_number is the error number, and this command must be written in the definition section.

Example:
Declare
E_inte exception; --Definition
pragma exception_intt (e_inte,-2291); --Associated Oracle Error ORA-2291
Begin
Update emp set Deptno=&dno where empno=&eno;
exception
When E_inte Then
Dbms_output.put_line (' department does not exist ');
End

Note that with Exception_init, a custom exception can only be connected to an Oracle error, in an exception-handling statement,
Instead of returning a user-defined message, Sqlcode and SQLERRM will return the code and message text for this Oracle error


Iv. Custom Exceptions

Description: A custom exception is not associated with an Oracle error and is explicitly thrown (raise) by the developer for exceptions defined for a particular case.

1. A brief description of the custom exception
Although the declaration of a custom exception is similar to the declaration of a variable, the exception is an error state, not a data item,
So an exception cannot appear in an assignment statement and an SQL statement, but the scope of the exception is the same as the scope of the other variable that defines the part.
If a custom exception is passed to an extraterritorial scope, it can no longer be referenced by the original name. To solve this problem,
We can declare an exception in the package, which can be used in any block, with the prefix of the package name before the exception.

Custom exceptions are generated by the Raise statement (the user-defined exception declared by the Exception_inti compilation directive can also be generated by the corresponding Oracle error),
Of course, if necessary, predefined exceptions can also be generated using the raise statement.

When an exception is generated, control is immediately transferred to the exception handling section of the block. If the block has no exception handling part,
is passed to the outer layer of the block. Once control is handed over to the exception handling section, there is no way to return to the block executable.

An exception-handling statement can handle multiple exceptions, as long as multiple exceptions are split by or in the When clause.

If an exception in a block is not handled, the block returns the program that called it with an unhandled exception, which causes the program that called it to make an error.
If an exception occurs during a stored procedure, the out parameter of the stored procedure will not get the return value. To avoid the drawbacks of unhandled exceptions,
We'd better use the others clause at the outermost of the block to handle all unhandled exceptions in the block. This ensures that all errors can be detected and handled.

An assignment statement that defines a section produces an exception. Even if there are processing statements in the exception handling section of the current block that handle the exception, it is not executed,
Instead, it is immediately passed to the outer block. When an exception is passed to an external block, the exception is handled as if it were the exception that was produced in the processing executable part.

An exception can also be generated in an exception-handling statement, which can be generated by a raise statement or by a running error.
The exceptions produced in both cases are immediately passed outside the block, as is the exception in the definition section. Why do you do that?
Because the exception part can only have one exception to be handled each time, when an exception is handled, another exception is generated,
You cannot handle more than one exception at a time, so the exception that is generated by the exception handling section is passed outside the block.

2. Simple application of Custom exception
Description: User-defined exception type, using raise display to throw an exception
Declare
v_name:= ' Mary ';
v_dno:=80;
E_integrity exception; --Define custom exceptions
E_no_rows exception;
pragma exception_init (e_integrity,-2291);
Name Emp.ename%type:=v_name;
DNO Emp.deptno%type:=v_dno;
Begin
Update emp set deptno=v_dno where Ename=v_name;
If Sql%notfound Then
Raise E_no_rows; --Show Throw exception
End If;
exception
When E_integrity Then
Dbms_output.put_line (' The sector does not exist ');
When E_no_rows Then
Dbms_output.put_line (' The employee does not exist ');
End

3, the raise throws the exception three kinds of methods
1), Raise exception: Used to throw exceptions that are defined in the current program or system exceptions in standard.
2), Raise package.exception: Used to throw some exceptions that are defined in non-standard packages, such as utl_file,dbms_sql and exceptions in packages created by programmers
3), Raise: Without any parameters, this only occurs when you want the current exception to be passed to the external program.


Five, the function of the exception

Note: When an exception occurs, error numbers and related error messages can be obtained by using the exception function, and error numbers and error messages can be customized by using Raise_application_error.

1, Sqlcode and SQLERRM functions

Sqlcode returns the error code number of the exception object, SQLERRM returns the corresponding error message, in order to handle other unexpected Oracle errors in Plsql,
You can refer to these two functions after the When others clause in the Exception handling section to determine the error number and information.

Exception type SQLCODE SQLERRM
Exception negative for Oracle error Oracle Error
No_data_found +100 No DATA FOUND
Custom exception-1 user-defined Exception
No exception generated 0 Oracle-0000

Note: If you use the Exception_init precompiled directive to declare a custom exception that is connected to an Oracle error, Sqlcode and SQLERRM return
The corresponding Oracle error code and corresponding error message instead of returning +1 and user-defined.

If SQLERRM is able to take a numeric parameter, the return value is the text associated with the numeric parameter.

If you want to use Sqlcode and SQLERRM in SQL statements, be sure to assign their values to local variables before you use them in SQL statements.
Because these functions are procedural in nature, they cannot be used directly in SQL statements.

EXCEPTION
When OTHERS
Then
ROLLBACK;
V_message: = ' ERROR line number: ' | | Dbms_utility.format_error_backtrace () | | ' Error code: ' | | sqlcode| | ' Error hints ' | | SQLERRM;
Dbms_output.put_line (V_message);
END;

2, Raise_application_error
Description: This procedure is used for custom error messages, only used by database terminal programs (procedures, functions, packages, triggers), not in anonymous blocks or in client terminal programs.
Syntax: Raise_application_error (Error_number,mesage[,[true|false]);
Where error_number defines the error number, the loss is a negative integer between 20000 and 20999, and the message specifies the error message, not longer than 2048 bytes;
The third is an optional parameter, and true causes the error to be placed on the stack of the previous error, Fale replaces all previous errors and defaults to False.

Example:
Create or Replace procedure Raise_comn (Eno number,commission number) is
V_comm Emp.comm%type;
Begin
Select Comm to V_comm from EMP where Empno=eno;
If V_comm is null then
Raise_application_error (-20001, ' No subsidy for the employee ');
End If;
exception
When No_date_found Then
Dbms_output.put_line (' Employee not present ');
End


Vi. PL/SQL compilation warnings
Description: Added after 10g, in order to improve the robustness of the Plsql subroutine and avoid running errors, you can activate the warning check function.

1. PL/SQL Warning classification
severs: Check for unexpected results or incorrect results, such as parameter aliases, that may occur.
Performance: Check for possible performance issues, such as providing VARCHAR2 data for the number column when the insert is executed.
Informational: Check the dead code in the subroutine.
All: Check all warnings (all 3 types are checked).

2. PL/SQL Warning message control
Note: To activate the warning function, you need to set the initialization parameter plsql_warnings.
Can be set through system level, session level, dbms_varnings System Package, ALTER procedure command.
You can activate or suppress all warnings or some kind of warning.

Alter system set plsql_warnings= ' Enable:all '; --System level
Alter session set plsql_warnings= ' Enable:performance '; --Session level
Call dbms_warning.set_warning_setting_string (' Enable:severs ', ' Session '); --dbms_varnings System Package
Alter procedure hello compile plsql_warnings= ' enable:performance '; --alter Procedure Command
Alter session set plsql_warnings= ' Disable:all ';

3. Use of PL/SQL warnings

1), Detect dead code
Create or replace procedure Dead_code as
X number: = 10;
Begin
If x=10 then x:=20;
Else
x:=100; --Dead code, never executed
End If;
End Dead_code;

Alter session set plsql_warnings= ' Enable:informational ';--activation
Alter PROCEDURE Dead_code compile;--compilation
Show errors;--Display

2), detect the code that causes the performance problem
Create or replace Precedure update_sql (name Varchar2,salary varchar2) is
Begin
Update emp set sql=salary where Ename=name;
End

Alter session set plsql_warnings= ' Enable:informational ';--activation
Alter PROCEDURE Dead_code compile;--compilation
Show errors;--Display

Oracle Exception (-)