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

    sys_refcursor resultset

    874273

      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;

       

      begin

       

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

         return v_cur;

      end;

       

      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..!!!

       

      Regards

      AP

        • 1. Re: sys_refcursor resultset
          AnnPricks E

          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;

          begin

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

             return v_cur;

          end;

          CREATE OR REPLACE FUNCTION test_fun1

          RETURN SYS_REFCURSOR

          AS

          v_in SYS_REFCURSOR;

          BEGIN

          OPEN v_in FOR SELECT fun_test(100) FROM dual;

            ----code here

          RETURN v_in;

          END;

           

          Message was edited by: 000000

          • 2. Re: sys_refcursor resultset
            Billy~Verreynne

            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>
            SQL>
            SQL> -- typical use of ref cursor interface from client
            SQL> --
            SQL> -- client defines a host variable to receive pointer
            SQL> var c refcursor
            SQL>
            SQL> -- client makes PL/SQL call using bind variables
            SQL> exec FooProc( 'A%', :c )
            
            PL/SQL procedure successfully completed.
            
            SQL>
            SQL> -- client processes cursor pointer (fetch and
            SQL> -- display in this case)
            SQL> print c
            
                DEPTNO   COUNT(*)
            ---------- ----------
                    20          1
                    30          1
            
            SQL>
            SQL>
            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;
            11
            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.
            
            SQL>