Standard Oracle PL/SQL Exception Handling

Environment: ORCLE 10G

An SQL statement is displayed separately. How to handle exceptions.

1) Standard PL/SQL blocks containing Exception Handling

DECLARE
No_Detail_record EXCEPTION;
PRAGMA EXCEPTION_INIT(No_Detail_record,-20000 );
V_nDetailCount number;
BEGIN
Select count (*) into v_nDetailCount from tson where .....;
If v_nDetailCount = 0 then
Raise_application_error(-20000 ,'');
End if;
EXCEPTION
WHEN no_Detail_record THEN
Dbms_output.put_line ('no detailed records ');
WHEN OTHERS THEN
Dbms_output.put_line ('unknown exception ');
END;

2) raise_application_error
Name and format of the function that throws an exception
RAISE_APPLICATION_ERROR (error_number, message [, {true | false}])
The first parameter in RAISE_APPLICATION_ERROR is the exception number, which can only be defined between [-20999,-20000.
However, according to my own test, if the number defined is smaller than-20999, the program can also be executed normally.
The default value of the third parameter is false, so the thrown exception replaces all other exceptions, which means you cannot handle other exceptions, replace it with the current exception (this is the choice of custom exceptions in most cases); if it is TRUE, the newly thrown exceptions are stored at the top of the exception stack (Other exceptions have occurred ).

3) Oracle pre-defined PL/SQL exceptions

Exception name	ORACODE	SQLCODE	Trigger time
LOGIN_DENIED	01017	-1017	Invalid user name or password
NO_DATA_FOUND	01403	100	Select into does not return rows, code points to the deleted elements in the nested table, or code points to the uninitialized elements in the index table (array.
NOT_LOGGED_ON	01012		No Logon. It is intended to execute some calls.
PROGRAM_ERROR	06501		Internal PL/SQL errors
ROWTYPE_MISMATCH	06504		When a cursor assigns a value to another cursor, especially when a subprocess containing the cursor parameter is called. If the two return types are inconsistent, it will appear.
SELF_IS_NULL	30625		An attempt is made to call a method member of an object without initialization. Because the keyword SELF indicates the SELF-production of the object (a built-in parameter), it is always the first method member to be passed to the method member during the call.
STORAGE_ERROR	06500		PL/SQL consumes memory, or memory leakage occurs.
SUBSCRIPT_BEYOND_COUNT	06533		In simple terms, it is out of bounds when accessing array objects (or nested tables.
SUBSCRIPT_OUTSIDE_LIMIT	06532		Similar to the preceding one, the index number here is invalid, for example,-1.
SYS_INVALID_ROWID	01410		An exception occurs when converting a string that does not conform to the ROWID format to ROWID.
TIMEOUT_ON_RESOURCE	00051		A timeout occurs when a resource is waiting.
TOO_MANY_ROWS	01422		The select into statement returns the result of an additional row.
VALUE_ERROR	06502		Number, conversion, truncation, or size constraints. For example, if the length of a variable is smaller than the column length, an exception occurs if you want to save the column value. In the process statement, if the string fails to be converted to a number, this will be triggered. In an SQL statement, an INVALID_NUMBER exception is triggered.
ZERO_DIVIDE	01476		Try to divide by 0.
Access_assist_null	06530		Attempts to access a property without an initialized object, which is similar to self_is_null
CASE_NOT_FOUND	06592		"No situation found" In a case when clause, no choice is satisfied, but there is no else clause.
COLLECTION_IS_NULL	06531		Try to access uninitialized arrays or nested tables by using collection methods other than exists, or try to assign values to them.
CURSOR_ALREADY_OPEN	06511		Attempt to re-open the opened cursor.
DUP_VAL_ON_INDEX	00001		Try to store duplicate values in the unique index column.
INVALID_CURSOR	01001		Attempts to perform some illegal operations on the cursor, such as trying to close a cursor that has not been opened.
INVALID_NUMBER	01722		Similar to value_error, it is only applicable to SQL statements and occurs when characters are converted to numbers.

Note: 1) sqlcode, usually =-1 * to_number (oracode) unless otherwise specified.
2) There are several groups of exceptions that are similar or highly correlated, for example:
INVALID_NUMBER and VALUE_ERROR
SELF_IS_NULL and ACCESS_INTO_NULL, COLLECTION_IS_NULL
3) exceptions with nested tables and Arrays
4) no_data_found is an exception that cannot be found. Its sqlcode = 100 instead of-1403