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 emp;
--
--
END;
various procedure redacted --
END;
/
SQL> @create_pkg_hr.pls
Package created. Contents of create_pkg_hr.pls follows:
--
-- hr.pls
-- The "CRUD indicator" is a letter following the last underscore in
-- the procedure's name. In this package the various procedures
-- that affect the employee table are:
--
-- employees_c() -- create an employee
-- employees_r() -- read employee details
-- employees_u() -- update employee details
-- employees_d() -- destroy an employee
--
CREATE OR REPLACE
PACKAGE hr
IS
--
--===========================================================
--
-- emp
--
-----------------------------------------------------------------------------------
various procedures redacted
-----------------------------------------------------------------------------------
--
-- Read all emp' details
--
PROCEDURE employees_r(
p_details IN OUT hr_child.empl_details_refcur_t
);
--
various procedures redacted --
END;
/
OK the emp table is created and the packages built without error. Now create a unit test. ut_hr.sql follows:
-- unittest
--
SET serverout ON
SET linesize 300
COLUMN name FORMAT A30
CREATE OR REPLACE
PROCEDURE read_employee
IS
BEGIN
DECLARE
io hr_child.empl_details_refcur_t;
v_retcode NUMBER := NULL;
BEGIN
hr.employees_r(io);
END;
DBMS_OUTPUT.PUT_LINE('v_employee_id, ' || '(' || TO_CHAR( io.EMPNO) || ')' );
END;
/
SQL> @ut_hr.sql
LINE/COL ERROR
-------- -----------------------------------------------------------------
12/5 PL/SQL: Statement ignored
12/66 PLS-00201: identifier 'IO.EMPNO' must be declared
so it appears that empno is not part of hr_child.empl_details_refcur_t; because hr_child.empl_details_refcur_t is a strong ref cursor it should match the columns in the emp table
SQL> desc emp
Name Null? Type
----------------------------------------- -------- -------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
SQL>
empno is a column in the emp table. So why must 'IO.EMPNO' must be declared?
The scope where the IO variable was declared has ended and, in the outer scope of the procedure there is no IO variable.
CREATE OR REPLACE PROCEDURE read_employee
IS
-- Declare section for outer scope
BEGIN -- Begin outer scope
DECLARE -- Declare section for inner scope
io hr_child.empl_details_refcur_t;
v_retcode NUMBER := NULL;
BEGIN -- Begin inner scope
hr.employees_r(io);
END; -- End inner scope
-- Continue outer scope
DBMS_OUTPUT.PUT_LINE('v_employee_id, ' || '(' || TO_CHAR( io.EMPNO) || ')' );
END;
/
To fix it, remove the inner PL/SQL block and move everything to the outer block. Once you do that you also need to fix that IO is a cursor and is not a row from the table and a cursor does not have column attributes so IO.EMPNO does not exist - to get the row you need to loop through the cursor and fetch the row and then row.EMPNO will exist:
CREATE OR REPLACE PROCEDURE read_employee
IS
io hr_child.empl_details_refcur_t;
rw EMP%ROWTYPE;
BEGIN
hr.employees_r(io);
LOOP
FETCH io INTO rw;
EXIT WHEN io%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('v_employee_id, (' || rw.EMPNO || ')' );
END LOOP;
CLOSE io;
EXCEPTION
WHEN OTHERS THEN
IF io%ISOPEN THEN
CLOSE io;
END IF;
RAISE;
END;
/