Partially consuming a cursor in multiple pl/sql calls without defining it in package spec

I have a problem to understand your question. But I can provide clarification for your ideas.

1. Opening the cursor as refcursor and storing refcursor variable in a dedicated package: impossible, as pl/sql doesn't allow sys_refcursor variables at spec levels

The workaround with dbms_sql.

create table test_rows as  (select level rr from dual connect by level <= 100);create or replace package cursor_ctx is  ctx_number integer;end;  declare  p_cursor sys_refcursor;begin open p_cursor for 'select rr from test_rows';   cursor_ctx.ctx_number := DBMS_SQL.TO_CURSOR_NUMBER(p_cursor);end;

This part consuming is data from the cursor.

declare  p_cursor sys_refcursor; type l_number is table of number; v_numbers l_number;begin  if  DBMS_SQL.IS_OPEN(cursor_ctx.ctx_number) then    p_cursor := DBMS_SQL.TO_REFCURSOR(  cursor_ctx.ctx_number);    fetch p_cursor bulk collect into v_numbers limit 10;        if v_numbers.count < 10 then             dbms_output.put_line('No more data, close cursor');            close p_cursor;            cursor_ctx.ctx_number := null;        else             cursor_ctx.ctx_number := DBMS_SQL.TO_CURSOR_NUMBER(p_cursor);        end if;        for i in nvl(v_numbers.first,1) .. nvl(v_numbers.last,-1) loop            dbms_output.put_line(v_numbers(i));           end loop;    else      dbms_output.put_line('Null or cursor close ');    end if;    end;

1. Pipelined function has future to split input cursor into chunk. Parallel Enabled Pipelined Table Functions

2. JDBC allows using sys_refcursor as an output parameter. sys_refcursor = ResultSet.