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.

The SQL inside the FOR LOOP would error out as PL/SQL would expect an INTO clause. — Lalit Kumar B, Nov 23 '15 at 10:17
Right now yes. Currently it is to demonstrate what I want to achieve. Maybe there is a way to do it without even using a loop, but I have been unable to find it. — ChK, Nov 23 '15 at 10:19
Yes, you do not need a cursor for loop. You could use OPEN CURSOR FOR statement. See the answer for more details and a working demo. — Lalit Kumar B, Nov 23 '15 at 10:50

Lalit Kumar B · Answer 1 · 2015-11-23T10:47:34.177

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;

You do not need the cursor FOR LOOP. You could use OPEN CURSOR FOR statement and return a SYS_REFCURSOR.

For example,

OPEN LS_CUR FOR SELECT * FROM DOCUMENT_QUEUE DQ 
WHERE DQ.ENV_ID IN (SELECT * FROM TABLE(LR_UPDATED_ROWS));

RETURN LS_CUR;

or,

OPEN LS_CUR FOR SELECT * FROM DOCUMENT_QUEUE DQ 
WHERE DQ.ENV_ID MEMBER OF LR_UPDATED_ROWS;

RETURN LS_CUR;

However, in order to do that, you must CREATE the type at SQL level not at PL/SQL level. Else, you would receive PLS-00642: local collection types not allowed in SQL statements.

A small demo:

Create the type at SQL level:

SQL> CREATE OR REPLACE TYPE table_typ AS TABLE OF NUMBER
  2  /

Type created.

Let's get the output in SQL*Plus using a refcursor:

Using MEMBER OF syntax:

SQL> variable r refcursor
SQL> DECLARE
  2    l_typ table_typ;
  3  TYPE numbers IS TABLE OF NUMBER;
  4    n numbers;
  5  BEGIN
  6    SELECT empno BULK COLLECT INTO l_typ FROM emp;
  7    OPEN :r FOR SELECT empno,
  8    ename FROM emp WHERE empno member OF l_typ;
  9  END;
 10  /

PL/SQL procedure successfully completed.

SQL> print r

     EMPNO ENAME
---------- ----------
      7369 SMITH
      7499 ALLEN
      7521 WARD
      7566 JONES
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7788 SCOTT
      7839 KING
      7844 TURNER
      7876 ADAMS
      7900 JAMES
      7902 FORD
      7934 MILLER

14 rows selected.

Using TABLE function:

SQL> variable r refcursor
SQL> DECLARE
  2    l_typ table_typ;
  3  TYPE numbers IS TABLE OF NUMBER;
  4    n numbers;
  5  BEGIN
  6    SELECT empno BULK COLLECT INTO l_typ FROM emp;
  7    OPEN :r FOR SELECT empno,
  8    ename FROM emp WHERE empno IN (SELECT * from TABLE(l_typ));
  9  END;
 10  /

PL/SQL procedure successfully completed.

SQL> print r

     EMPNO ENAME
---------- ----------
      7369 SMITH
      7499 ALLEN
      7521 WARD
      7566 JONES
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7788 SCOTT
      7839 KING
      7844 TURNER
      7876 ADAMS
      7900 JAMES
      7902 FORD
      7934 MILLER

14 rows selected.

How can I solve this without going to the SQL level? I am unfortunately unable to access the SQL level. Is there a way to solve the same task without using the table type? (Getting the list of id's that were updated) — ChK, Nov 24 '15 at 07:45

score 2 · Accepted Answer · answered Nov 23 '15 at 10:40

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;

This is the solution for my current problem as I am unable to create types outside of PL/SQL and I have to return a SYS_REFCURSOR from the function. — ChK, Nov 27 '15 at 06:11

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

2 Answers2