How to execute a Stored Procedure with RECORD TYPE as OUT parameter

Question

This is the package specification:

create or replace PACKAGE EMPLOYEE_DETAILS AS

        TYPE DETAILS IS RECORD( 
        EMPLOYEE_ID NUMBER(6,0),
          EMPLOYEE_FIRST_NAME VARCHAR2(20),
          EMPLOYEE_LAST_NAME VARCHAR2(25)
          );

        TYPE TABLE_EMPLOYEES IS TABLE OF DETAILS;

        PROCEDURE GET_EMPLOYEES(
        EMP_DEPT_ID EMPLOYEES.DEPARTMENT_ID%TYPE,
        EMP_SALARY employees.salary%TYPE,
        TBL_EMPLOYEES OUT TABLE_EMPLOYEES
        );

        END EMPLOYEE_DETAILS;

And this is the package body. I was able to compile the package but need some help on executing the stored procedure to verify the results.

create or replace PACKAGE BODY EMPLOYEE_DETAILS AS

    PROCEDURE GET_EMPLOYEES(
    EMP_DEPT_ID EMPLOYEES.DEPARTMENT_ID%TYPE, 
    EMP_SALARY employees.salary%TYPE,
    TBL_EMPLOYEES OUT TABLE_EMPLOYEES
)

IS
    LC_SELECT SYS_REFCURSOR;
    LR_DETAILS DETAILS;
    TBL_EMPLOYEE TABLE_EMPLOYEES;

BEGIN
    OPEN LC_SELECT FOR 
        SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME 
        FROM EMPLOYEES
        WHERE DEPARTMENT_ID=EMP_DEPT_ID
        AND EMPLOYEES.SALARY>EMP_SALARY;

    LOOP 
        FETCH LC_SELECT INTO LR_DETAILS;
        EXIT WHEN LC_SELECT%NOTFOUND;

        IF IS_EMPLOYEE(LR_DETAILS.EMPLOYEE_ID) THEN
            TBL_EMPLOYEE.extend();
            TBL_EMPLOYEE(TBL_EMPLOYEE.count()) := LR_DETAILS;
        END IF;
    END LOOP;
    CLOSE LC_SELECT;
    TBL_EMPLOYEES := TBL_EMPLOYEE;

END GET_EMPLOYEES;
END EMPLOYEE_DETAILS;

What I've have so far is:

set serveroutput on
declare 
tbl_employees table_employees;
begin
employee_details.get_employees(30,1000,tbl_employees);

For i IN tbl_employees.First .. tbl_employees.Last Loop
dbms_output.put_line(tbl_employees(i).employee_id || ' ' ||
                                tbl_employees(i).first_name|| ' ' ||
                                tbl_employees(i).last_name);
End Loop;
end;

But when I execute this it gives me error saying

table_employees must be declared

and the other one is

PLS-00320: the declaration of the type of this expression is incomplete or malformed.

Can somebody please help me with this?

Solution 1

You are referring the record type incorrectly.

tbl_employees table_employees;

table_employees must be declared

You are using a record type and not an object type. Thus, you cannot refer the record type as a database object. You need to refer it as the package object you created.

You need to refer the record type as:

l_table_rec_type employee_details.table_employees

Let's look at a complete test case:

SQL> CREATE OR REPLACE
  2  PACKAGE employee_details
  3  AS
  4  TYPE details
  5  IS
  6    RECORD
  7    (
  8      p_name   VARCHAR2(40),
  9      p_emp_id NUMBER );
 10  TYPE table_employees
 11  IS
 12    TABLE OF details;
 13    PROCEDURE get_employees(
 14        p_deptno IN emp.deptno%TYPE,
 15        p_sal IN emp.sal%TYPE,
 16        emp_rec OUT table_employees );
 17  END employee_details;
 18  /

Package created.

SQL>

Package is created,

SQL> CREATE OR REPLACE
  2  PACKAGE BODY employee_details
  3  AS
  4  PROCEDURE get_employees(
  5        p_deptno IN emp.deptno%TYPE,
  6        p_sal IN emp.sal%TYPE,
  7        emp_rec OUT table_employees )
  8  IS
  9  BEGIN
 10    SELECT ename, empno BULK COLLECT INTO emp_rec FROM scott.emp where deptno = p_deptno and sal > p_sal;
 11  END get_employees;
 12  END employee_details ;
 13  /

Package body created.

SQL>

Package body is also created. Now let,s call the procedure.

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2    l_table_rec_type employee_details.table_employees;
  3  BEGIN
  4    dbms_output.put_line(' calling get_employees ');
  5    employee_details.get_employees(30, 1000, l_table_rec_type);
  6    FOR l_rec IN 1..l_table_rec_type.count
  7    LOOP
  8      dbms_output.put_line('employee details ' || l_table_rec_type(l_rec).p_name ||' '||l_table_rec_type(l_rec).p_emp_id);
  9    END LOOP;
 10  END;
 11  /
calling get_employees
employee details ALLEN 7499
employee details WARD 7521
employee details MARTIN 7654
employee details BLAKE 7698
employee details TURNER 7844

PL/SQL procedure successfully completed.

SQL>

You have the required output.

Solution 2

Since table_employees is defined inside the package employee_details hence use

tbl_employees employee_details.table_employees := employee_details.table_employees();

Also do the same change for initializing collection in package body

create or replace PACKAGE BODY EMPLOYEE_DETAILS AS

PROCEDURE GET_EMPLOYEES(
EMP_DEPT_ID EMPLOYEES.DEPARTMENT_ID%TYPE, 
EMP_SALARY employees.salary%TYPE,
TBL_EMPLOYEES OUT TABLE_EMPLOYEES
)

IS
LC_SELECT SYS_REFCURSOR;
LR_DETAILS DETAILS;
TBL_EMPLOYEE TABLE_EMPLOYEES:= table_employees();

Comments

[kris]

@ psaraj12 thank you but when I execute now it gives me error saying :ORA-06531: Reference to uninitialized collection ORA-06512: at "HR.EMPLOYEE_DETAILS", line 26 ORA-06512: at line 4 06531. 00000 - "Reference to uninitialized collection" *Cause: An element or member function of a nested table or varray was referenced (where an initialized collection is needed) without the collection having been initialized. *Action: Initialize the collection with an appropriate constructor or whole-object assignment. Thank you.

[psaraj12]

Initialized the collection

[kris]

@ psaraj12 I still get the same error after I make the changes suggested by you.

[Lalit Kumar B]

There is no need to initialize a table of records, you would need that for a collection of objects. See my answer.

[psaraj12]

@LalitKumarB kindly explain why user is getting the below error ORA-06531: Reference to uninitialized collection ORA-06512: at "HR.EMPLOYEE_DETAILS", line 26 ORA-06512: at line 4 06531. 00000 - "Reference to uninitialized collection" *Cause: An element or member function of a nested table or varray was referenced (where an initialized collection is needed) without the collection having been initialized. *Action: Initialize the collection with an appropriate constructor or whole-object assignment.

[psaraj12]

@LalitKumarB please comment after proper review

[Lalit Kumar B]

I usually upvote most of your answers. And I comment seldom when I think I have something to add. You seem to have misunderstood. I have reviewed each and every line of OP's code, and I have rewritten the entire code and posted a working test case.

[psaraj12]

there is no problem in commenting ,i had suggested initialization only after OP has got the initilization error ,may be some cases initilization is required for record type

[Sachin Kumar]

Thanks for detailed explanation and code. It worked. Bulk insert is a really great, saved me from creating a cursor.

[Lalit Kumar B]

@SachinKumar Glad it helped!