Return SYS_REFCURSOR using a function without using TYPE

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,
E.ENAME,
D.DNAME,
D.DEPTNO
FROM SCOTT.EMP E,
SCOTT.DEPT D
WHERE e.deptno =d.deptno;

Function for the above SQL:

SQL> CREATE OR REPLACE
FUNCTION GETEMPDATA(
P_EMPNO NUMBER )
RETURN sys_refcursor
IS
retCur sys_refcursor;
BEGIN
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 ;
RETURN RETCUR;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(sqlerrm);
dbms_output.put_line(dbms_utility.format_error_backtrace);
RETURN NULL;
END GETEMPDATA;

So now when I run the function I get the result as below:

image

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;

image

Finally by the help of another expert named slightwv (From Experts-Exchange)I was able to get the result as the output of my function was in XML.

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:

SQL> select
*
from
xmltable(
‘/ROWSET/ROW’
PASSING xmltype(GETEMPDATA(7369))
columns
EMPNO  PATH ‘EMPNO’,
ENAME PATH ‘ENAME’,
DNAME PATH  ‘DNAME’ ,
DEPTNO PATH ‘DEPTNO’
);

image

We can use multiple parameter to our function and can add more columns as per our requirement.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s