Oracle PL/SQL collect values from a loop into a cursor

Question

I have a PL/SQL TABLE TYPE result set that contains document ids.
I can loop over the result set without a problem, but the issue is that I have to return a sys_refcursor from the function, but I am unable to collect the values from the loop into the cursor.

TYPE table_typ IS TABLE OF DOCUMENT_QUEUE.ENV_ID%TYPE INDEX BY PLS_INTEGER;

FUNCTION GET_DOCS()
RETURN SYS_REFCURSOR
IS
   LS_CUR SYS_REFCURSOR;
   LR_UPDATED_ROWS table_typ;
BEGIN
      UPDATE DOCUMENT_QUEUE DQ
      ...
      RETURNING DQ.ENV_ID BULK COLLECT INTO LR_UPDATED_ROWS;               

      -- Need to collect all of the following rows into the cursor
      FOR indx IN NVL (LR_UPDATED_ROWS.FIRST, 0) .. NVL (LR_UPDATED_ROWS.LAST, -1)
      LOOP
          SELECT * FROM DOCUMENT_QUEUE DQ WHERE DQ.ENV_ID = LR_UPDATED_ROWS(indx);
      END LOOP;

      RETURN LS_CUR;        
END GET_DOCS;

All help and hints are welcome.

Answer 1

For the mentioned requirement i have mentioned below a snippet which will help to fetch all the rows into ref cursor for every rowid. Let me know if this helps.

CREATE OR REPLACE TYPE table_typ
IS
  TABLE OF DOCUMENT_QUEUE.ENV_ID%TYPE INDEX BY PLS_INTEGER;


CREATE OR REPLACE FUNCTION GET_DOCS
RETURN SYS_REFCURSOR
IS
  LS_CUR SYS_REFCURSOR;
  LR_UPDATED_ROWS table_typ;
  lv_rows_lst VARCHAR2(32676);
BEGIN
  SELECT <COL1> BULK COLLECT INTO LR_UPDATED_ROWS FROM <TABLE_NAME>;
  FOR I IN LR_UPDATED_ROWS.FIRST..LR_UPDATED_ROWS.LAST
  LOOP
    lv_rows_lst:=lv_rows_lst||','||LR_UPDATED_ROWS(I);
  END LOOP;
  lv_rows_lst:=SUBSTR(lv_rows_lst,2,LENGTH(lv_rows_lst));
  OPEN LS_CUR FOR 'SELECT * FROM DOCUMENT_QUEUE DQ WHERE DQ.ENV_ID IN ('||lv_rows_lst||')';
  RETURN LS_CUR;
END GET_DOCS;