ERRBUFF and RETCODE

Detailed Explanation on the usage of ERRBUFF and RETCODE:

If we register any concurrent program’s executable as pl/sql stored procedure we need to pass two mandatory parameters to that
                1.ERRBUFF
                2.RETCODE.

ERRBUFF needs to be passed as the first parameter and RETCODE as the second one. Both of these are OUT variable type. ERRBUFF is VARHCAR2 and RETCODE can be VARCHAR2/NUMBER.

To ERRBUFF we can assign the reason for the ERROR/WARNING,it could be a user defined message or we can assing SQLERRM.

To RETCODE we can assign below numbers

                0-For Success

                1-For Warning

                2-For Error

**IF anything is not assigned to RETCODE, by default the program will complete SUCCESS**

Ø What to do to complete the Concurrent Program in WARNING:

Create a stored procedure in database as below, this procedure will go into EXCEPTION block if the data entered for p_emp parameter not available in emp table.

In the EXCEPTION block assign the values to ERRBUFF and RETCODE as given,so that the program will complete in WARNING.

CREATE OR REPLACE PROCEDURE test_out_var_proc(errbuff  OUT  VARCHAR2,

                                                         retcode   OUT  VARCHAR2,

                                                         p_emp  IN NUMBER)

AS

v_name VARCHAR2(200);

BEGIN

    SELECT ename

    INTO     v_name

    FROM    scott.emp

    WHERE  empno=p_emp;

EXCEPTION

WHEN OTHERS THEN

errbuff:='Raising WARNING as entered EMP no there in system';

retcode:=1;

END;

Create an Executable with this procedure and then concurrent program with a parameter as shown below, after that attach this to required request group.

Now submit the Program with some junk EMP number, so that the program will go into EXCEPTION block of the procedure

Entered 8465 as input for p_emp, which will make the program to go into EXCEPTION and as we have assigned  RETCODE =1,program will be completed in WARNING

And the ERRBUFF can be seen by clicking the View Log button of the concurrent program

Ø What to do to complete the Concurrent Program in ERROR:

Use the same stored procedure, in WHEN OTHERS exception just use the below values for ERRBUF&RETCODE instead of previous values and compile the procedure. This procedure will go into EXCEPTION if the data entered for p_emp parameter not available in emp table.

errbuff:='Raising ERROR as entered EMP no there in system';

retcode:=2;

Now submit the concurrent program again for some junk employee number.As shown below program will complete in ERROR status and ERRBUFF can be seen by clicking ‘Details’ button is clicked. Also can be seen in the Log.