Dec 19, 2013

Concurrent Programs Warning-Error: ERRBUFF and RETCODE


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.

----------------------------------------------------------
-- Package Specification
----------------------------------------------------------
CREATE OR REPLACE PACKAGE apps.emp_test_pkg
IS
   FUNCTION emp_name (
      errbuff       OUT  NOCOPY  VARCHAR2,
      retcode       OUT  NOCOPY  VARCHAR2,
      p_emp_number  IN           NUMBER)
      RETURN  VARCHAR2;
    
END emp_test_pkg;

/

----------------------------------------------------------
-- Package Body
----------------------------------------------------------
CREATE OR REPLACE PACKAGE BODY apps.emp_test_pkg
IS
 
   FUNCTION emp_name (
      errbuff       OUT  NOCOPY  VARCHAR2,
      retcode       OUT  NOCOPY  VARCHAR2,
      p_emp_number  IN           NUMBER)
      RETURN  VARCHAR2
   IS
      lv_emp_name   VARCHAR2(300DEFAULT  NULL;
    
   BEGIN
       
      SELECT (papf.first_name || ' ' || papf.last_name)
        INTO lv_emp_name
        FROM per_all_people_f  papf
       WHERE 1=1
         AND papf.employee_number = p_emp_number;
    
      RETURN (lv_emp_name);
    
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         errbuff := 'No employee found for ' || p_emp_number;
         retcode := '1';        -- warning
       
         fnd_file.put_line(fnd_file.log, errbuff);
         RETURN (lv_emp_name);
       
      WHEN OTHERS THEN
       
         errbuff := SQLERRM;
         retcode := '2';        -- error
       
         fnd_file.put_line(fnd_file.log, errbuff);
         RETURN (lv_emp_name);
 
   END emp_name;
 
END emp_test_pkg;
/


No comments: