Concurrent Programs Warning-Error: ERRBUF and RETCODE

The ERRBUF 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.

NOCOPY Hint to Improve Performance of OUT and IN OUT Parameters in PL/SQL

 

Oracle has two methods of passing passing OUT and IN OUT parameters in PL/SQL code:

  • Pass By Value : The default action is to create a temporary buffer (formal parameter), copy the data from the parameter variable (actual parameter) to that buffer and work on the temporary buffer during the lifetime of the procedure. On successful completion of the procedure, the contents of the temporary buffer are copied back into the parameter variable. In the event of an exception occurring, the copy back operation does not happen.
  • Pass By Reference : Using the NOCOPY hint tells the compiler to use pass by reference, so no temporary buffer is needed and no copy forward and copy back operations happen. Instead, any modification to the parameter values are written directly to the parameter variable (actual parameter).

———————————————————-

— 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(300)  DEFAULT  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;

/

Advertisements