Retrieve error code and error message in pl/sql

In soa we can use the runtimeFault.wsdl to get the code,summary and details of error messages

or we have the option to get it using prebuild function in advanced column

ora:getfaultAsString() and ora:getFault()

In a similar way we have something in pl/sql which can be used to retrieve the exact error code and error message.

This is required as we often need to capture these details and pass it to the error table so that it can be passed to the BAM for monitoring.

We will see and example on how to capture the error code and error message in pl/sql

Create an error table

CREATE TABLE error_table (
ErrorCode NUMBER,
ErrorMessage VARCHAR2(64),
ErrorTime TIMESTAMP);


Now make sure you have a employee table

IN my case it has an element fullname and employeeid

SET SERVEROUTPUT ON
DECLARE
name employee.fullname%type;
error_code NUMBER;
error_msg VARCHAR2(64);
BEGIN
SELECT fullname INTO name
FROM EMPLOYEE
WHERE EMPLOYEEID = -420;
EXCEPTION
WHEN OTHERS THEN
error_code := SQLCODE;
error_msg := SUBSTR(SQLERRM, 1, 64);
DBMS_OUTPUT.PUT_LINE
('Error code ' || error_code || ': ' || error_msg);


INSERT INTO error_table
VALUES (error_code, error_msg, SYSTIMESTAMP);
END;
/


So once you will execute this code you can go and check the error table it will be populated with the error details

Since you have already used set server output on you will be able to see the error in screen also as below

anonymous block completed
Error code 100: ORA-01403: no data found


There are two variable "SQLCODE" and "SQLERRM" which we have used to retrieve the error code and error message.

These are system defined code and can not be used directly , in order to use them we must assign them to some variable.

Now in an ideal fault handling scenario we can call a procedure in the fault handling block.

One important thing to note here is that when we call a procedure in fault handling block we must call the procedure define with

PRAGMA AUTONOMOUS_TRANSACTION

This is again similar to transaction concept in Oracle SOA suite.

When you define a procedure of function with PRAGMA AUTONOMOUS_TRANSACTION the procedure or function will run in a separate transaction.

that means it will be independent of the primary block where it is getting called.