3 Replies Latest reply: Jan 28, 2014 5:46 AM by Hoek RSS

    sys_refcursor resultset


      Hi Masters,


      I have created a function which will return result set from emp table. I have to utilize this resultset in another procedure/function. Is it possible?

      I have writtne a function like this..


      create or replace function test (v_sal in number) return sys_refcursor is

      v_cur sys_refcursor;




         open v_cur for select * from emp where sal>=v_sal;

         return v_cur;



      SQL> select test(2000) from dual;


      6 rows displayed.


      I need to use this records to filter in another subprogram? is it possible? Please advise..!!!




        • 1. Re: sys_refcursor resultset

          You can call this function in another subprogram like below. But can you please explain your requirement so we can able give a better solution

          create or replace function fun_test (v_sal in number) return sys_refcursor is

          v_cur sys_refcursor;


             open v_cur for select * from emp where sal>=v_sal;

             return v_cur;


          CREATE OR REPLACE FUNCTION test_fun1



          v_in SYS_REFCURSOR;


          OPEN v_in FOR SELECT fun_test(100) FROM dual;

            ----code here

          RETURN v_in;



          Message was edited by: 000000

          • 2. Re: sys_refcursor resultset

            A cursor (reference, explicit or implicit) is NOT a physical result set!


            A cursor is like a program - that outputs data. Each fetch call made, executes that cursor, resulting in it finding the next matching row(s) in the database, and returning these to the caller.


            A ref cursor is a pointer to the cursor created in server memory. This pointer can then be passed by the code that created the cursor (typically PL/SQL), to the calling code (typically .Net or Java) - enabling that client code to execute that cursor and receive its output data (if any).


            Simplistic example:

            SQL> create or replace procedure FooProc( nameMask in varchar2, refCur in out sys_refcursor ) is
              2  begin
              3          open refCur for
              4                  select deptno, count(*) from emp where ename like nameMask group by deptno
              5                  order by 2 desc, 1 asc;
              6  end;
              7  /
            Procedure created.
            SQL> -- typical use of ref cursor interface from client
            SQL> --
            SQL> -- client defines a host variable to receive pointer
            SQL> var c refcursor
            SQL> -- client makes PL/SQL call using bind variables
            SQL> exec FooProc( 'A%', :c )
            PL/SQL procedure successfully completed.
            SQL> -- client processes cursor pointer (fetch and
            SQL> -- display in this case)
            SQL> print c
                DEPTNO   COUNT(*)
            ---------- ----------
                    20          1
                    30          1
            SQL> -- from a PL/SQL perspective a ref cursor would
            SQL> -- be handled as follows:
            SQL> declare
              2          deptNo          integer;
              3          empCount        integer;
              4          c               sys_refcursor;
              5  begin
              6          FooProc( 'A%', c );
              7          loop
              8                  -- bulk fetch should be used
              9                  fetch c into deptNo, empCount;
            10                  exit when c%NotFound;
            12                  dbms_output.put_line(
            13                          'There is/are '||empCount||
            14                          ' matching employee(s) in department '||deptNo
            15                  );
            16          end loop;
            17          close c;
            18  end;
            19  /
            There is/are 1 matching employee(s) in department 20
            There is/are 1 matching employee(s) in department 30
            PL/SQL procedure successfully completed.