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: