Few days back I was trying to write a function which will return me a set of records where I can utilize it for my reporting purpose. I was using Pipelined Table function to return the data and finally with one SQL query I was able to retrieve the expected result. If I use a simple function then also I need to define an object type for my cursor result to hold the data .
I was thinking how dynamically I can use the same function without using a TYPE (Either creating or declaring) and then I used SYS_REFCURSOR as my return type in my function .
Below are the SQL and function as an example to show how to achieve this.
SQL query :
SQL> SELECT E.EMPNO,
FROM SCOTT.EMP E,
WHERE e.deptno =d.deptno;
Function for the above SQL:
SQL> CREATE OR REPLACE
P_EMPNO NUMBER )
OPEN RETCUR FOR SELECT E.EMPNO, E.ENAME, D.DNAME, D.DEPTNO FROM SCOTT.EMP E, SCOTT.DEPT D WHERE e.deptno=D.DEPTNO AND e.EMPNO =P_EMPNO ;
WHEN OTHERS THEN
So now when I run the function I get the result as below:
Or even if using a CUSROR to fetch the data was not giving me the proper result :
SQL> select cursor(select GETEMPDATA(7369) from dual) from dual;
So slightwv suggested to use a XML table to get a proper result.
So finally I got the result when I parsing the function into a XML table as like below:
EMPNO PATH ‘EMPNO’,
ENAME PATH ‘ENAME’,
DNAME PATH ‘DNAME’ ,
DEPTNO PATH ‘DEPTNO’
We can use multiple parameter to our function and can add more columns as per our requirement.