Testability of procedure with out parameter as sys_refcursor/nested table

[Somen Choudhury]

Hi all,

In our project we are trying if we can recommend utPlsql for unit testing, because it has the fetaure of integration with jenkin. So obviously very curious to know the every possible testing scenarios!

I had a few questions:
1. Can I test a procedure (standalone/packaged) which has an out parameter with data type: nested table?
2. Can I test a procedure (standalone/packaged) which has an out parameter with data type: sys_refcusor?

For more clarity I am representing the scenarios with examples:

-- I've an object type as:
CREATE OR REPLACE TYPE APPS.emp_obj as object
(EMPNO NUMBER(4)
,ENAME VARCHAR2(10)
,JOB VARCHAR2(9)
,MGR NUMBER(4)
,HIREDATE DATE
,SAL NUMBER(7,2)
,COMM NUMBER(7,2)
,DEPTNO NUMBER(2));
/

-- I've an nested table based on that object type:
CREATE OR REPLACE TYPE APPS.emp_obj_TAB_TYPE AS TABLE OF emp_obj;
/

-- Finally I've a procedure:
CREATE OR REPLACE procedure APPS.sp_emp (p_deptno in emp.deptno%type, p_emp_dept out emp_obj_tab_type) as
cursor c is
select *
from emp
where deptno=p_deptno;

type t_emp is table of c%rowtype;
l_emp t_emp := t_emp();
l_emp_dept emp_obj_tab_type := emp_obj_tab_type();

begin
open c;

loop
fetch c bulk collect into l_emp LIMIT 3;
exit when l_emp.count=0;
if l_emp.count > 0 then

 for i in l_emp.first .. l_emp.last loop        
   l_emp_dept.extend();
   l_emp_dept(l_emp_dept.last) := emp_obj(l_emp(i).EMPNO,l_emp(i).ENAME,l_emp(i).JOB,l_emp(i).MGR,l_emp(i).HIREDATE,l_emp(i).SAL,l_emp(i).COMM,l_emp(i).DEPTNO);
 end loop;

end if;

end loop;
close c;
p_emp_dept := l_emp_dept;
end;
/

-- And I also have a procedure which have a out parameter with type: sys_refcursor:
CREATE OR REPLACE procedure APPS.pr_emp(p_deptno in emp.deptno%type, p_out out sys_refcursor) as
begin
open p_out for select * from emp where deptno=p_deptno;
end;
/

So will I be able to perform unit test on sp_emp and pr_emp using utPlsql framework? If yes then please reply with some examples.
Also wanted to know if I can get code coverage statistics of the procedure being tested.

Will appreciate highly if someone can put any light on the above.

Thanks and regards,
S. Choudhury