Exceptions to Oracle's knowledge classification

Oracle Exception classification

1. Oracle predefined exception (that is, a defined inner exception)
2. Oracle Non-predefined exception (that is, undefined inner exception)
3. User-defined exceptions

Common pre-defined exceptions

The No_data_found statement does not query for a record that matches the criteria returned
Too_many_rows statement that matches a condition record has multiple returns
Dup_val_on_index a unique index in a database table, the program tries to store two duplicate values
Value_error when converting a character type, interception or length restriction occurs, such as the length of the declaration is shorter than the character
Storage_Error Memory Overflow
Zero_drivide Divisor is zero
Cast_not_found Case statement does not match the condition, and there is no else statement
Cursor_already_open program tries to open a cursor that is already open
Timeout_on_resource system is waiting for a resource, the system times out
Access_into_null The object must be initialized before assigning a value to the object
Invalid_number cannot convert characters to numbers
Transaction_backed_out due to deadlock commit was exited

common non-predefined exceptions                                                                                                                           

If you want to handle an undeclared inner exception, you must use the others exception handler or pragma exception_init
Others
pragma exception_init (exception_name,-oracle_error_number)

Usage:

DECLARE
Deadlock_detected EXCEPTION;
PRAGMA Exception_init (deadlock_detected,-60); --Associating an Oracle internal error number
BEGIN
...--Some operation that causes an ORA-00060 error
EXCEPTION
When deadlock_detected then-automatically triggered by Oracle system
--Handle the error
END;

ORA-00001: Violation of the unique constraint (.)
ORA-00017: Request session to set trace events
ORA-00018: Maximum number of sessions exceeded
ORA-00019: Maximum session license exceeded
ORA-00020: Maximum number of processes exceeded ()
ORA-00021: Session is attached to some other process; Unable to convert session
ORA-00022: Invalid session ID; Access denied
ORA-00023: Session reference process private memory; Unable to detach session
ORA-00024: Not allowed to register from multiple processes in single process mode
ORA-00025: Unable to allocate
ORA-00026: Missing or invalid session ID
ORA-00027: Unable to delete current session
ORA-00028: Your session has already been deleted
ORA-00029: Session is not a user session
ORA-00030: The user session ID does not exist.
ORA-00031: Mark the session to be deleted
ORA-00032: Invalid session migration password
ORA-00033: The current session has an empty migration password
ORA-00034: cannot be in the current PL/SQL session
Ora-00035:license_max_users cannot be less than the current number of users
ORA-00036: exceeds the maximum of recursive SQL () levels
ORA-00037: Cannot convert to a session belonging to a different server group
ORA-00038: Unable to create session: Server group belongs to other user
ORA-00050: Operating system error while getting queued
ORA-00051: Waiting for resource timeout
ORA-00052: Maximum number of queued resources exceeded ()
ORA-00053: Maximum number of enqueue exceeded
ORA-00054: The resource is busy and requires the specified NOWAIT
ORA-00055: Maximum number of DML locks exceeded
ORA-00056: DDL lock on object '. ' Hangs in incompatible mode
ORA-00057: Maximum number of temporary table locks exceeded
Ora-00058:db_block_size must be installed for this database (non)
ORA-00059: Maximum value exceeding Db_files
ORA-00060: Deadlock detected while waiting for resource
ORA-00061: Another routine is set up with a different dml_locks
ORA-00062: Unable to get DML full table lock; Dml_locks is 0
ORA-00063: Maximum number of log_files exceeded
ORA-00064: The object is too large to be allocated in this O/S (,)
Initialization of Ora-00065:fixed_date failed
Ora-00066:log_files for but needed to be compatible
ORA-00067: Value pair parameter is invalid; must be at least
ORA-00068: The value pair parameter is invalid and must be between and
ORA-00069: Unable to get lock--table lock disabled
ORA-00070: Invalid command
ORA-00071: The process number must be between 1 and
ORA-00072: Process "" Inactive
ORA-00073: command is used between a parameter and a
ORA-00074: No Process specified
ORA-00075: The process "" is not found in this routine
ORA-00076: Dump not found
ORA-00077: Dump is invalid
ORA-00078: Unable to dump variable by name
ORA-00079: Variable not found
ORA-00080: Invalid global zone specified by hierarchy
ORA-00081: Address range [,] not readable
ORA-00082: The memory size is not in the valid set [1], [2], [4] within
ORA-00083: Warning: Could damage the mapped SGA
ORA-00084: The global zone must be PGA, SGA, or UGA
ORA-00085: The current call does not exist
ORA-00086: User call does not exist
ORA-00087: Command failed to execute on remote routine
ORA-00088: Shared server failed to execute command
Invalid routine number in the Ora-00089:oradebug command
ORA-00090: Failed to allocate memory to the cluster database Oradebug command
The ora-00091:large_pool_size must be at least
Ora-00092:large_pool_size must be greater than large_pool_min_alloc
ORA-00093: Must be between and
ORA-00094: integer value required
ORA-00096: Value pair argument is invalid, it must come from between
ORA-00097: Using the Oracle SQL feature is not in the SQL92 level
ORA-00099: A timeout occurred while waiting for the resource, possibly due to a PDML deadlock
ORA-00100: No data found
ORA-00101: Invalid description for system parameter DISPATCHERS
ORA-00102: Scheduler cannot use network protocol
ORA-00103: Invalid network protocol, standby for scheduler
ORA-00104: Deadlock detected; All public servers are locked waiting for resources

Custom Exception                                                                                                                                         

Both pre-defined and non-predefined exceptions are related to Oracle internal errors and are implicitly triggered when an Oracle error occurs;
A custom exception is not associated with an Oracle error, it is an exception defined by the developer for a particular case and is triggered manually.
Steps
1. Defining exceptions
2. Triggering an exception (raise or Raise_application_error)
3. Exception Handling

For example:

DECLAREERROR1 EXCEPTION; ERROR2 EXCEPTION;BEGIN  IF 5 = 5  ThenRAISE ERROR1; elsif5 > 6  ThenRAISE ERROR2; END IF; EXCEPTION whenERROR1 ThenDbms_output. Put_Line ('5 equals 5 .');  whenERROR2 ThenDbms_output. Put_Line ('5 greater than 5');END;

RAISE the exception name, not only can trigger a custom exception, but also can trigger a system exception.
Raise_application_error (Error_number,message[,true,false]) is used to throw an exception and assign an error number to the exception (default is +1), error message (default = User_defined_ Exception)
The range of error numbers is 20,000 to-20,999. The error message is a text string, up to 2048 bytes. TRUE, the new error is added to the list of errors that have been raised. If Keep_errors=false (default), the new error replaces the current list of errors.

Raise_application_error ( -20123, ' Invald product Code ', TRUE);

Propagation of anomalies

The exception part of the PL/SQL program block contains code that handles the error, and when an exception is triggered, an exception trap occurs automatically, and the control leaves the execution part into the Exception handling section, and once the program enters the exception handling part it cannot go back to the execution part of the same block. The following is the general syntax for 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.]

When the others then must be placed on the last side of the exception handling, as the default processing does not show the exception of processing, if the exception occurs, the Oracle
Did not find the appropriate when ... Then statement, the code after then is executed, and if the exception handling code simply exits the corresponding nested block,
The program will continue executing the statement after the inner block end, so if you do not want to continue down after an exception, and return is necessary, if no corresponding exception is found within the same piece, then the same internal when OTHERS Then. If there is no OTHERS then in the same block, Oracle looks for exception handling up one level. If the inner layer is handled abnormally, no more processing is done to the outer look-up exception.

For example

BEGIN  DECLAREDefined_exc EXCEPTION; BEGINRAISE Defined_exc; --trigger abnormal, control steering;EXCEPTION whenDefined_exc ThenDbms_output.put_line ('Defined_exc'); END; --Defined_exc After exception handling, control goes hereEXCEPTION whenOTHERS Then  --control does not go here from Defined_exc exception because the inner layer defined_exc has been processedEND;

Exceptions to Oracle's knowledge classification