I’m trying to create some stored procedures, one of which accepts a strong reference cursor as a parameter. Although I think I’ve correctly specify the strong reference cursor I encounter an error when I attempt to create a unit test for this stored procedure.

The following commands were executed by SYSTEM:

    CREATE USER HR IDENTIFIED BY HR;
    grant CREATE session to hr;
    grant create any procedure to HR;
    the following is how I connect to Oracle via sqlplus:
    sqlplus hr/HR@connectionstring:7152 

contents of create_emp_table.sql:

DROP TABLE "HR"."EMP";
CREATE TABLE "HR"."EMP"
(
  EMPNO            NUMBER(4)                             PRIMARY KEY,
  ENAME            VARCHAR2(10),                                  
  JOB              VARCHAR2(9),                                   
  MGR              NUMBER(4),                                     
  HIREDATE         DATE,                                          
  SAL              NUMBER(7,2),                                   
  COMM             NUMBER(7,2),                                   
  DEPTNO           NUMBER(2)                                     
);
END;

SQL> @create_emp_table.sql

Table dropped.
Table created.


SQL>  @create_pkgbdy_hr.plb

Package body created.

SQL> @create_pkgbdy_hr-child.plb
Sequence dropped.
Sequence created.
Package body created.
Contents of create_pkgbdy_hr.plb:

CREATE sequence  employees_seq INCREMENT BY 1 START WITH 1;
CREATE OR REPLACE
PACKAGE BODY hr_child
IS

-- Read all employee details
   PROCEDURE get_all_emp_details(
                                   p_details IN OUT hr_child.empl_details_refcur_t
                                )   
IS    BEGIN

  --
  OPEN p_details FOR
     SELECT *
     FROM   emp;
  --
END;

—

various procedures redacted
END;
/

Contents of create_pkg_hr-child.pls follows.
Note that  empl_details_refcur_t is a strong reference cursor based on the emp table

CREATE OR REPLACE
PACKAGE hr_child
IS
   --
   TYPE empl_details_refcur_t IS REF CURSOR RETURN emp%ROWTYPE;   
   TYPE dept_details_refcur_t IS REF CURSOR RETURN dept%ROWTYPE;
   --===========================================================
various procedures redacted
END;
/

SQL> @create_pkgbdy_hr.plb

Package body created.

    --
    -- contents of create_pkgbdy_hr.plb follows:
    --
    CREATE OR REPLACE
    PACKAGE BODY hr
    IS
       --
    various procedures redacted   --
       -----------------------------------------------------------------------------------
       --
       -- obtain details for all employees
       --
       PROCEDURE employees_r(
                               p_details IN OUT hr_child.empl_details_refcur_t
                            )
       IS
       BEGIN
          --
          OPEN p_details FOR
             SELECT *
             FROM   employees;
          --
          --
       END;
    various procedure redacted   --
    END;
    /