In this article, you will learn about one of the error-handling procedures in the Oracle database called the raise_application_error.
Oracle Raise_Application_Error Procedure
The raise_application_error in Oracle is a stored procedure that enables you to raise the user-defined error messages.
When working with the Oracle database, you may have come across an error message such as the one in the following:
This type of error is known as a database engine error. It is defined in the Oracle database and raised when a given error occurs.
Using the raise_application_error procedure, Oracle allows us to define our custom messages with a specific error code and message.
This can be very useful when a specific error occurs and you wish to alert the user with a descriptive error message. This means that you will not have unhandled the error exceptions with no statement about what happened.
Procedure Syntax and Parameters
The following code snippet establishes the syntax of the raise_application_error procedure:
error_number,
message,
[{TRUE | FALSE}]
);
The procedure is comprised of three main parameters:
- Error_number – This parameter represents a specific integer value which is mapped to an error code. The value of this parameter ranges from -20000 to -20999.
- Message – This is a string value that denotes the user-defined error message to display to the user. The value of the message parameter has a length of up to 70 bytes.
- Keeperrorstack – This parameter is a Boolean value that determines whether the error stack should be preserved. By default, the procedure does not maintain the error stack.
NOTE: Preserving the error stack means that the error is added to the series of previous errors. If the stack is not preserved, the new error message replaces all the previous errors.
The following are some essential points to note for the raise_application_error procedure:
- The database engine automatically and immediately halts the execution of the parent block when it encounters the raise_application_error stored procedure.
- The procedure forces a rollback for all the operations made to the OUT and IN OUT parameters.
- The raise_application_error procedure rollback feature does not affect the global data structures such as database objects. However, if you wish to perform a rollback on such objects, you must call the rollback operation explicitly.
Example Raise_Application_Error Stored Procedure Usage
In this example, we are going to demonstrate how to use the raise_application_error procedure using the HR schema.
https://docs.oracle.com/cd/B13789_01/server.101/b10771/scripts003.htm
Consider the following provided source code:
emp_id number
)
is
fname EMPLOYEES.FIRST_NAME%type;
em EMPLOYEES.EMAIL%type;
sal EMPLOYEES.SALARY%type;
commission EMPLOYEES.COMMISSION_PCT%type;
begin
select FIRST_NAME, EMAIL, SALARY, COMMISSION_PCT
into fname, em, sal, commission
from EMPLOYEES
where EMPLOYEE_ID = emp_id;
if commission is null then
raise_application_error(-20002, 'Null Commission percentage encountered!!' || emp_id);
end if;
exception
when others then
DBMS_OUTPUT.PUT_LINE('SQLCODE: ' || SQLCODE);
DBMS_OUTPUT.PUT_LINE('SQLERRM: ' || SQLERRM);
end;
The previous example starts by creating a procedure that accepts an employee ID. We then use the employee to select the values from the employees. Finally, we use an IF statement to check if the commission of the received row is null. If it is true, we raise an exception.
We can call the procedure with the target employeed id:
Output:
SQLERRM: SQL0438N Application raised error or warning with diagnostic text: "Null Commission percentage encountered:. SQLSTATE=UD030
Conclusion
In this tutorial, you learned how to use the raise_application_error stored procedure to raise the custom error messages.
