Oracle Exception Handling Method

In the write and storage process, you sometimes need to return the cause of the error to facilitate viewing records.

Oracle provides the following exceptions:

1. pre-defined exception: used to handle common Oracle errors.

2. Non-pre-defined exception: used to handle Oracle errors that cannot be handled by a pre-defined exception.

3. custom exception: used to handle other cases unrelated to Oracle errors.

Exception Handling starts with the keyword exception. The syntax is as follows:

Exception
WhenPrediction_nameThen--Prediction_nameIs the abnormal name
Statement1;

 WhenOthersThen
Statement1;

 

 Common system exceptions:

cause of the named system exception
access_0000_null undefined object
If case_not_found case does not contain the corresponding when, when else is not set, the
collection_is_null collection element is not initialized.
the curser_already_open cursor has been opened.
the unique index of dup_val_on_index has duplicate values.
invalid_cursor is invalid
invalid_number embedded SQL statements cannot convert characters into numbers
no_data_found No rows are returned using select, or when the index table is not initialized

When too_many_rows executes select into, the result set exceeds one row.
The zero_divide divisor is 0.
Subscript_beyond_count element subscript exceeds the maximum value of nested tables or varray
When subscript_outside_limit uses a nested table or varray, the subscript is specified as a negative number.
When value_error is assigned a value, the variable length is insufficient to accommodate actual data.
Login_denied ApplicationProgramAn incorrect user name or password is provided when you connect to the Oracle database.
The not_logged_on PL/SQL application accesses data without connecting to the oralce database.
Program_error PL/SQL internal problems, you may need to reinstall the data dictionary & pl./SQL System package
Rowtype_mismatch: the host cursor variable is incompatible with the PL/SQL cursor variable return type
When self_is_null uses the object type, the object method is called on the null object.
When storage_error runs PL/SQL, the memory space is exceeded.
Invalid sys_invalid_id rowid string
Timeout_on_resource Oracle timeout while waiting for resources

 

Example:

Declare
E_integrity exception;-- 1 Definition section
Pragma exception_init(E_integrity, -2291);-- 2. Establish Association
Begin
UpdateEMPSetDeptno=: DNOWhereEmpno= :Eno;
Exception
WhenE_integrityThen-- 3 , Capture Processing
Dbms_output.Put_line(' This department does not exist ');
End;