ERRBUFF and RETCODE with Execution method

The ERRBUFF can be returned with any message.

The RETCODE can be returned with one of three values:

   0  -- Success
   1  -- Warning
   2  -- Error

Below is an example of a package where I can pass an employee number and it can return its full name. If no data found for the employee number passed, the concurrent program will turn YELLOW with the message 'No Employee Found'. If there is any other error occurs, it will turn RED with SQLERRM message.

----------------------------------------------------------

-- Specification

----------------------------------------------------------

CREATE OR REPLACE PROCEDURE xx_item_update_proc(errbuff      OUT  NOCOPY VARCHAR2,
                                                    retcode      OUT  NOCOPY VARCHAR2) is

----------------------------------------------------------

-- Exception Handling 

----------------------------------------------------------

EXCEPTION

 WHEN  no_data_found

   THEN

      DBMS_OUTPUT.PUT_LINE (SQLCODE || '_' || SQLERRM);

      errbuff := 'No Data Found ' || SQLCODE || '_' || SQLERRM ;

         retcode := '1';                                           -- warning

        fnd_file.put_line(fnd_file.log, errbuff);

      

WHEN OTHERS

   THEN

     errbuff := SQLERRM || '    ' || SQLCODE;

      retcode := '2';      -- error

      fnd_file.put_line(fnd_file.log,errbuff);

----------------------------------------------------------

-- Execution method from Toad

----------------------------------------------------------

DECLARE

   v_errbuff   VARCHAR2(250);

   v_retcode   VARCHAR2(250);

BEGIN

   v_errbuff := NULL;

   v_retcode := NULL;

   xx_item_update_proc (v_errbuff, v_retcode);

END;