REF CURSOR parameters in PLSQL Web Services

If you have ever tried to publish a PLSQL package as a Web Service that has a method signature as follows JDeveloper 10.1.3.x doesn't allow you.

procedure test1 (pRefCursor1 out sys_refcursor, prefCursor2 out sys_refcursor);

However the following does work for a function returning a refcursor.

function retrieve_emps return sys_refcursor;

If you press the "Why Not" button in the PLSQL Web Service dialog you get this message.

"Ref cursor type arguments are unsupported due to a JDBC limitation. Ref cursor types are only supported for use as the return type".

Due to this limitation if you have the luxury of developing the PLSQL from scratch you can simple use PLSQL tables to overcome this. So lets assume we have a collection defined as follows.

DROP TYPE employee_list;
DROP TYPE employee_obj;

CREATE OR REPLACE TYPE employee_obj AS OBJECT
( empno  NUMBER(4)
, ename  VARCHAR2(10)
, job    VARCHAR2(9)
, sal    NUMBER(7,2)
, deptno NUMBER(2)
);
/

create or replace TYPE employee_list AS TABLE OF employee_obj;

So now we can use a method signature as follows and this will work fine.

procedure copy_emps (pInEmps in employee_list, pOutEmps out employee_list);