CODEWITHSUNDEEP
sqloracle-databaseoracle11gsys-refcursor
Ron M
Ron M

Reputation: 803

Fetch SYS_REFCURSOR into a table with unknown columns

I have a procedure which returns a SYS_REFCURSOR as an OUT parameter. What I would like to do is call that procedure, and use that returned SYS_REFCURSOR in another procedure. In this second procedure, I would need to run a GROUP BY on the results, and return it in another SYS_REFCURSOR.

The problem I'm encountering is that I do not know the name or number of columns in advance.

Does anyone have any ideas for a solution? Thank you in advance.

Upvotes: 2

Views: 1107

Answers (1)

finidigeorge
finidigeorge

Reputation: 149

Actually, you can do this, but it requires some effort and for most cases it's just overkill.

The way is the next sequence of steps:

  1. Create XMLTYPE from sys_refcursor
  2. Parse sys_refcursor via dbms_sql, get information about columns
  3. Create dynamic sql, which will be select from XMLTYPE via xpath, based on information from step 2

simple example, to illustrate the idea:

select EMPNO, count(*) from xmltable( ‘/ROWSET/ROW’ PASSING xmltype(CURSOR( SELECT E.EMPNO, E.ENAME, D.DNAME, D.DEPTNO FROM SCOTT.EMP E, SCOTT.DEPT D WHERE e.deptno=D.DEPTNO )) columns EMPNO PATH ‘EMPNO’, ENAME PATH ‘ENAME’, DNAME PATH ‘DNAME’ , DEPTNO PATH ‘DEPTNO’ ) group by EMPNO

Upvotes: 3

Related Questions