Return multiple ref cursors in single OUT parameter of stored procedure

Question

For the procedure below, I need to return the resultset of multiple dynamic queries into a single refcursor or any other type of output parameter.

Please note that in my project, I may have more than one query. I'm not looking to join the data, as I need to use them separately in Java code once the proc is read using JDBC code.

But PLSQL does not allow me to use a collection table of cursors.

Below is the sample code which i used,

CREATE OR REPLACE PROCEDURE multiple_cursor_out_proc (
p_in        VARCHAR2,
p_cur   OUT SYS_REFCURSOR)
AS
    v_sql_1   CLOB := 'select  * from table_2';
    v_sql_2   CLOB := 'select  * from table_2';
    .
    .
    .
    v_sql_n  CLOB := 'select  * from table_n';

BEGIN
/* how to return all results in p_cur  ? */
END;

Answer 1

Build a dynamic SQL statement with a CURSOR expression for every query:

CREATE OR REPLACE PROCEDURE multiple_cursor_out_proc (
p_in        VARCHAR2,
p_cur   OUT SYS_REFCURSOR)
AS
    v_sql_1  CLOB := 'select 1 a, 2 b from dual';
    v_sql_2  CLOB := 'select 3 c from dual';
    v_sql_3  CLOB := 'select 4 d from dual';
BEGIN
    open p_cur for
    '
        select
            cursor('||v_sql_1||') cursor1,
            cursor('||v_sql_2||') cursor2,
            cursor('||v_sql_3||') cursor3
        from dual
    ';
END;
/

Below is an example of how to use and view the cursors. This works in PL/SQL Developer by clicking on "<Cursor>" repeatedly.

create or replace function test_function return sys_refcursor is
    v_output sys_refcursor;
begin
    multiple_cursor_out_proc(1, v_output);
    return v_output;
end;
/

select test_function from dual;