Oracle convert ref cursor to table

Oracle convert ref cursor to table:

We will check in this article possible ways to convert a Ref cursor to a Table.

We need to create a setup to test the scenario.

First we will create a package to get the reference of a permanent ref cursor.

Then we will create a Record Type and then its corresponding Table type in database. Whenever we call a Ref cursor as a table then the related objects must be stored in database. Otherwise oracle will unable to perform the operation.

SETUP:

--Oracle convert ref cursor to table : Setup

CREATE TABLE EMPLOYEES_REF_TAB
AS
SELECT EMPLOYEE_ID,
       FIRST_NAME,
       LAST_NAME,
       JOB_ID,
       SALARY,
       DEPARTMENT_ID
FROM EMPLOYEES
WHERE 1 = 2;

--Oracle convert ref cursor to table : Setup

CREATE OR REPLACE PACKAGE EMP_PKG
IS
  TYPE RC_1 IS REF CURSOR RETURN EMPLOYEES_REF_TAB%ROWTYPE;
END;
/

--Oracle convert ref cursor to table : Setup

CREATE OR REPLACE TYPE EMP_REF_TYPE_REC IS OBJECT
(
 EMPLOYEE_ID    NUMBER(6),    
 FIRST_NAME     VARCHAR2(20), 
 LAST_NAME      VARCHAR2(25),         
 JOB_ID         VARCHAR2(10), 
 SALARY         NUMBER(8,2),    
 DEPARTMENT_ID  NUMBER(4)
);
/

CREATE OR REPLACE TYPE EMP_REF_TYPE_TAB IS TABLE OF EMP_REF_TYPE_REC;
/

Converting Ref Cursor to a Table using normal function:

--Oracle convert ref cursor to table : Example 

CREATE OR REPLACE FUNCTION FUN_EMP_REC_TEST(REC_IN EMP_PKG.RC_1)
RETURN EMP_REF_TYPE_TAB
IS
  TYPE T_EMP_TAB IS TABLE OF EMPLOYEES_REF_TAB%ROWTYPE INDEX BY PLS_INTEGER;
  V_EMP_TAB T_EMP_TAB;
  P_EMP_TAB EMP_REF_TYPE_TAB := EMP_REF_TYPE_TAB();
BEGIN
  LOOP
    FETCH REC_IN BULK COLLECT INTO V_EMP_TAB LIMIT 100;
    EXIT WHEN V_EMP_TAB.COUNT = 0;
    --
    FOR I IN 1..V_EMP_TAB.COUNT
    LOOP
      P_EMP_TAB.EXTEND;
      P_EMP_TAB(P_EMP_TAB.LAST) := EMP_REF_TYPE_REC(V_EMP_TAB(I).EMPLOYEE_ID,
                                                    V_EMP_TAB(I).FIRST_NAME,
                                                    V_EMP_TAB(I).LAST_NAME,
                                                    V_EMP_TAB(I).JOB_ID,
                                                    V_EMP_TAB(I).SALARY,
                                                    V_EMP_TAB(I).DEPARTMENT_ID
                                                   );
    END LOOP;
  END LOOP;
  CLOSE REC_IN;
  RETURN P_EMP_TAB;  
END;
/

Querying the Ref cursor data as a table:

SELECT * FROM TABLE(FUN_EMP_REC_TEST(CURSOR(SELECT EMPLOYEE_ID, 
                                                   FIRST_NAME, 
                                                   LAST_NAME, 
                                                   JOB_ID, 
                                                   SALARY, 
                                                   DEPARTMENT_ID 
                                            FROM EMPLOYEES WHERE DEPARTMENT_ID = 30
                                           )
                                    )
                    );

Using Pipelined Table function to convert a Ref Cursor to a Table:

--Oracle convert ref cursor to table : Example

CREATE OR REPLACE FUNCTION FUN_EMP_REC_TEST(REC_IN EMP_PKG.RC_1)
RETURN EMP_REF_TYPE_TAB
PIPELINED
IS
  TYPE T_EMP_TAB IS TABLE OF EMPLOYEES_REF_TAB%ROWTYPE INDEX BY PLS_INTEGER;
  V_EMP_TAB T_EMP_TAB;
  P_EMP_TAB EMP_REF_TYPE_TAB := EMP_REF_TYPE_TAB();
BEGIN
  LOOP
    FETCH REC_IN BULK COLLECT INTO V_EMP_TAB LIMIT 100;
    EXIT WHEN V_EMP_TAB.COUNT = 0;
    --
    FOR I IN 1..V_EMP_TAB.COUNT
    LOOP
      PIPE ROW(EMP_REF_TYPE_REC(V_EMP_TAB(I).EMPLOYEE_ID,
                                V_EMP_TAB(I).FIRST_NAME,
                                V_EMP_TAB(I).LAST_NAME,
                                V_EMP_TAB(I).JOB_ID,
                                V_EMP_TAB(I).SALARY,
                                V_EMP_TAB(I).DEPARTMENT_ID
                               )
              );
    END LOOP;
  END LOOP;
  RETURN;  
END;
/

SELECT * FROM TABLE(FUN_EMP_REC_TEST(CURSOR(SELECT EMPLOYEE_ID, 
                                                   FIRST_NAME, 
                                                   LAST_NAME, 
                                                   JOB_ID, 
                                                   SALARY, 
                                                   DEPARTMENT_ID 
                                            FROM EMPLOYEES WHERE DEPARTMENT_ID = 30
                                           )
                                    )
                    );

RELATED TOPICS:

• Working With Cursors
• How To Pass Array in where clause in Oracle?
• How to pass array in Oracle SQL query?
• Oracle Nested table as stored procedure parameters
• How to pass collection in stored procedure in Oracle?