Oracle Database allows user-defined errors in PL/SQL code to be handled so that user-specified error numbers and messages are returned to the client application, which can handle the error.
User-specified error messages are returned using the RAISE_APPLICATION_ERROR procedure. For example:
RAISE_APPLICATION_ERROR(error_number, 'text', keep_error_stack)
This procedure stops subprogram execution, rolls back any effects of the subprogram, and returns a user-specified error number and message (unless the error is trapped by an exception handler). error_number must be in the range of -20000 to -20999.
Use error number -20000 as a generic number for messages where it is important to relay information to the user, but having a unique error number is not required. Text must be a character expression, 2 KB or less (longer messages are ignored). To add the error to errors on the stack, set Keep_error_stack to TRUE; to replace the existing errors, set it to FALSE (the default).
The RAISE_APPLICATION_ERROR procedure is often used in exception handlers or in the logic of PL/SQL code. For example, this exception handler selects the string for the associated user-defined error message and invokes the RAISE_APPLICATION_ERROR procedure:
...
WHEN NO_DATA_FOUND THEN
SELECT Error_string INTO Message
FROM Error_table,
V$NLS_PARAMETERS V
WHERE Error_number = -20101 AND Lang = v.value AND
v.parameter = "NLS_LANGUAGE";
Raise_application_error(-20101, Message);
...
Topics:
User-defined exceptions are explicitly defined and raised within the PL/SQL block, to process errors specific to the application. When an exception is raised, the usual execution of the PL/SQL block stops, and an exception handler is invoked. Specific exception handlers can be written to handle any internal or user-defined exception.
Application code can check for a condition that requires special attention using an IF statement. If there is an error condition, then two options are available:
Enter a RAISE statement that names the appropriate exception. A RAISE statement stops the execution of the subprogram, and control passes to an exception handler (if any).
Invoke the RAISE_APPLICATION_ERROR procedure to return a user-specified error number and message.
You can also define an exception handler to handle user-specified error messages. The following example shows:
An exception and associated exception handler in a subprogram
A conditional statement that checks for an error (such as transferring funds not available) and enters a user-specified error number and message within a trigger
How user-specified error numbers are returned to the invoking environment (in this case, a subprogram), and how that application can define an exception that corresponds to the user-specified error number
Declare a user-defined exception in a subprogram or package body (private exceptions), or in the specification of a package (public exceptions), and efine an exception handler in the body of a subprogram (standalone or package), as follows:
PROCEDURE fire_emp (empid NUMBER) IS
invalid_empid EXCEPTION
PRAGMA EXCEPTION_INIT (invalid_empid, -20101);
BEGIN
DELETE FROM emp WHERE empno = empid;
EXCEPTION
WHEN invlid_empid THEN
INSERT INTO emp_audit
VALUES (empid, 'Fired before probation ended');
END
This program returns the error number 20101 to the invoking environment (the preceding program), and a user-defined before-delete trigger named emp_probation checks for that error number, as follows:
TRIGGER emp_probation
BEFORE DELETE ON emp
FOR EACH ROW
BEGIN
IF (sysdate - :old.hiredate) < 30 THEN
raise_application_error(20101,
'Employee' || old.ename || ' on probation')
END IF;
END;
In database PL/SQL units, an unhandled user-error condition or internal error condition that is not trapped by an appropriate exception handler causes the implicit rollback of the program unit. If the program unit includes a COMMIT statement before the point at which the unhandled exception is observed, then the implicit rollback of the program unit can only be completed back to the previous COMMIT.
Additionally, unhandled exceptions in database-stored PL/SQL units propagate back to client-side applications that invoke the containing program unit. In such an application, only the application program unit invocation is rolled back (not the entire application program unit), because it is submitted to the database as a SQL statement.
If unhandled exceptions in database PL/SQL units are propagated back to database applications, modify the database PL/SQL code to handle the exceptions. Your application can also trap for unhandled exceptions when invoking database program units and handle such errors appropriately.
You can use a trigger or a stored subprogram to create a distributed query. This distributed query is decomposed by the local Oracle Database instance into a corresponding number of remote queries, which are sent to the remote nodes for execution. The remote nodes run the queries and send the results back to the local node. The local node then performs any necessary post-processing and returns the results to the user or application.
If a portion of a distributed statement fails, possibly from a constraint violation, then Oracle Database returns ORA-02055. Subsequent statements, or subprogram invocations, return ORA-02067 until a rollback or a rollback to savepoint is entered.
Design your application to check for any returned error messages that indicates that a portion of the distributed update has failed. If you detect a failure, rollback the entire transaction (or rollback to a savepoint) before allowing the application to proceed.
When a subprogram is run locally or at a remote location, these types of exceptions can occur:
PL/SQL user-defined exceptions, which must be declared using the keyword EXCEPTION
PL/SQL predefined exceptions, such as NO_DATA_FOUND
SQL errors, such as ORA-00900
Application exceptions, which are generated using the RAISE_APPLICATION_ERROR procedure.
When using local subprograms, all of these messages can be trapped by writing an exception handler, such as:
EXCEPTION
WHEN ZERO_DIVIDE THEN
/* Handle the exception */
The WHEN clause requires an exception name. If the exception that is raised does not have a name, such as those generated with RAISE_APPLICATION_ERROR, then one can be assigned using PRAGMA_EXCEPTION_INIT. For example:
DECLARE
...
Null_salary EXCEPTION;
PRAGMA EXCEPTION_INIT(Null_salary, -20101);
BEGIN
...
RAISE_APPLICATION_ERROR(-20101, 'salary is missing');
...
EXCEPTION
WHEN Null_salary THEN
...
When invoking a remote subprogram, exceptions are also handled by creating a local exception handler. The remote subprogram must return an error number to the local invoking subprogram, which then handles the exception, as shown in the previous example. Because PL/SQL user-defined exceptions always return ORA-06510 to the local subprogram, these exceptions cannot be handled. All other remote exceptions can be handled in the same manner as local exceptions.