Oracle exception usage

1.RAISE_APPLICATION_ERRORDefinition and use

The procedureRAISE_APPLICATION_ERRORLets you issue User-DefinedORA-Error messages from stored subprograms. That way, you can report errors to your application and avoid returning unhandled exceptions.

To callRAISE_APPLICATION_ERROR, Use the syntax

raise_application_error(error_number, message[, {TRUE | FALSE}]);

Whereerror_numberIs a negative integer in the range-20000...-20999 andmessageIs a character string up to 2048 bytes long. If the optional third parameter isTRUE, The error is placed on the stack of previous errors. If the parameter isFALSE(The default), the error replaces all previous errors.RAISE_APPLICATION_ERRORIs part of PackageDBMS_STANDARD, And as with packageSTANDARD, You do not need to qualify references to it.

An application can callraise_application_errorOnly from an executing stored subprogram (or method). When called,raise_application_errorEnds the subprogram and returns a user-defined error number and message to the application. The error number and message can be trapped like any Oracle error.

In the following example, you callraise_application_errorIf an employee's salary is missing:

Create procedure raise_salary (emp_id number, amount number)
Curr_sal number;
Begin
Select Sal into curr_sal from EMP where empno = emp_id;
If curr_sal is null then
/* Issue user-defined error message .*/
Raise_application_error (-20101, 'salary is missing ');
Else
Update EMP set sal = curr_sal + amount where empno = emp_id;
End if;
End raise_salary;

2. Combined with exception_init, custom exception

The calling application gets a PL/SQL exception, which it can process using the error-reporting functionsSQLCODEAndSQLERRMInOTHERSHandler. Also, it can use the PragmaEXCEPTION_INITTo map specific error numbers returnedraise_application_errorTo sort tions of its own, as the following Pro * C example shows:

Exec SQL Execute
/* Execute embedded PL/SQL block using host
Variables my_emp_id and my_amount, which were
Assigned values in the host environment .*/
Declare
Null_salary exception;
/* Map error number returned by raise_application_error
To user-defined exception .*/
Pragma exception_init (null_salary,-20101 );
Begin
Raise_salary (: my_emp_id,: my_amount );
Exception
When null_salary then
Insert into emp_audit values (: my_emp_id ,...);
End;
End-exec;