ERRBUFF and RETCODE  PL/Sql Mandatory Parameters for Concurrent Programs

In Oracle EBS, we can use Concurrent Programs to call a procedure within a package. Every PL/SQL procedure of packages which is being called in a concurrent program must have two mandatory OUT parameters – ERRBUFF and RETCODE

ERRBUF : is used to get error messages.
RETCODE: is used to get the status of the concurrent program. It can take any of the following values

0 – Concurrent Program is successful.
1 – Concurrent program completed with warning.
2 – Concurrent Program has some Error.

Why Do we Have This

Different business have different needs. These two mandatory parameters makes it flexible for the developers to end a concurrent program in error or warning as per their custom needs.

For eg : Lets say we have a procedure that computes salary of a person. A negative salary is an error scenario for the business but not for oracle. As a result concurrent program will end up in success on completion of request. But using RETCODE we can make the concurrent program to end in error by setting its value to 2 and in the similar way we can add our custom error message to the ERRBUF parameter.

Sample Procedure Declaration

CREATE OR REPLACE PACKAGE XX_CUSTOM_PKG PROCEDURE validate_salary (X_ERRBUF OUT VARCHAR2, X_RETCODE OUT NUMBER, P_USER_ID IN NUMBER); BEGIN . . . EXCEPTION WHEN NEGATIVE_SALARY THEN X_ERRBUF : 'Negative salary computed. Incorrect data'; X_RETCODE = 2; END;

TAG: