RAISE_APPLICATION_ERROR associates an error number with an error text. Therefore, when working with the RAISE_APPLICATION_ERROR there is no need to create an exception name.
RAISE_APPLICTION_ERROR has two required parameters, error_number and error_text. The keep_error is an optional parameter that is usually omitted.
Generally, Oracle associates negative numbers with runtime errors. For user-defined exceptions, the range of such numbers is from -20,000 to -20,999.
When the RAISE_APPLICATION_ERROR procedure is used, control is always passed to the host environment. For example, if an exception is raised in an inner block, it never propagates to the outer block.
EXCEPTION_INIT pragma is a special instruction to the compiler. It allows handling of unnamed internal exceptions. Such exceptions can also be handled with the help of OTHERS.
Because pragma is a special instruction to the compiler, it is processed during compilation time.
Some Oracle errors do not have names, and as a result they cannot be referenced in a program. The EXCEPTION_INIT pragma allows you to associate an Oracle error number with a user-defined error.
In order to associate an Oracle error with a user-defined exception, the EXCEPTION_INIT pragma requires both error number and name.
Error_number is a numeric parameter and should contain any valid Oracle error number; 'ORA' is not a part of an error number.
When no exception is raised, the SQLCODE function returns 0. When there is a NO_DATA_FOUND exception, SQLCODE function returns 100.