CODEWITHSUNDEEP
oracle-databaseplsqloracle11g
Christian
Christian

Reputation: 93

print refcursor from function

I'm using oracle 11 XE & Application Express 4.0.2.00.09 and I have this function that I want to print it's cursor. I don't know how to read the return of this function when it's called.

CREATE OR REPLACE FUNCTION stuff 
  RETURN sys_refcursor IS
  rf_cur sys_refcursor;
BEGIN
   OPEN rf_cur FOR
   SELECT ename 
     FROM emp 
    WHERE sal = (SELECT MAX(sal) 
                   FROM emp);    
  RETURN rf_cur;
END;

Upvotes: 0

Views: 119

Answers (1)

Littlefoot
Littlefoot

Reputation: 142710

A simple option is to just select it (as any other function), but I guess that's not what you want:

SQL> select stuff from dual;

STUFF
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

ENAME
----------
KING

Another option requires PL/SQL (I guess that is what you're looking for):

SQL> set serveroutput on
SQL> declare
  2    l_rc    sys_refcursor;
  3    l_ename emp.ename%type;
  4  begin
  5    l_rc := stuff;
  6    fetch l_rc into l_ename;
  7    dbms_output.put_line(l_ename);
  8  end;
  9  /
KING

PL/SQL procedure successfully completed.

SQL>

Upvotes: 2

Related Questions