4 Replies Latest reply: Aug 14, 2008 11:14 AM by Satyaki_De RSS

    Passing cursor as parameter

    582561
      Hi,

      Can anyone give a small example of passing a cursor to a stored procedure or function.

      Also if there is doc available for this plz let me know.


      Thanx
        • 2. Re: Passing cursor as parameter
          Satyaki_De
          Try with this -
          create or replace procedure gen_cur(str in varchar2,rc in out sys_refcursor)
          is
               str1 varchar2(500);
          begin
               str1 := str;
               open rc for str1;
          exception
          when others then
               dbms_output.put_line(sqlerrm);
          end;
          
          variable b refcursor
          
          declare
               src varchar2(300);
          begin
               src:= 'select empno,ename,hiredate,sal
                      from emp
                      where sal between 2000 and 7000';
               gen_cur(src,:b);
          exception
          when others then
               dbms_output.put_line(sqlerrm);
          end;
          
          print b
          Regards.

          Satyaki De.

          N.B.: Not Tested....
          • 3. Re: Passing cursor as parameter
            616326
            Here is a real quick one...
            FUNCTION open_query (p_curs SYS_REFCURSOR)
            RETURN NUMBER IS
                v_num NUMBER;
            BEGIN
                FETCH p_curs INTO v_num;
               
                RETURN v_num;
            END open_query;
            And then call this as:
            SELECT open_query(CURSOR(SELECT order_number FROM orders WHERE customer_number = 12345))
               FROM dual;
            Of course, this assumes that only one row is returned.
            • 4. Re: Passing cursor as parameter
              Satyaki_De
              Well what i've posted is working. Here is the actual test -
              satyaki>
              satyaki>select * from v$version;
              
              BANNER
              ----------------------------------------------------------------
              Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
              PL/SQL Release 10.2.0.3.0 - Production
              CORE    10.2.0.3.0      Production
              TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
              NLSRTL Version 10.2.0.3.0 - Production
              
              Elapsed: 00:00:00.36
              satyaki>
              satyaki>
              satyaki>create or replace procedure gen_cur(str in varchar2,rc in out sys_refcursor)
                2  is
                3   str1 varchar2(500);
                4  begin
                5   str1 := str;
                6   open rc for str1;
                7  exception
                8  when others then
                9   dbms_output.put_line(sqlerrm);
               10  end;
               11  /
              
              Procedure created.
              
              Elapsed: 00:00:01.07
              satyaki>
              satyaki>
              satyaki>variable b refcursor
              satyaki>
              satyaki>declare
                2   src varchar2(300);
                3  begin
                4   src:= 'select empno,ename,hiredate,sal
                5          from emp
                6          where sal between 2000 and 7000';
                7   gen_cur(src,:b);
                8  exception
                9  when others then
               10   dbms_output.put_line(sqlerrm);
               11  end;
               12  /
              
              PL/SQL procedure successfully completed.
              
              Elapsed: 00:00:00.17
              satyaki>
              satyaki>
              satyaki>print b
              
                   EMPNO ENAME      HIREDATE         SAL
              ---------- ---------- --------- ----------
                    7566 JONES      02-APR-81       2975
                    7698 BLAKE      01-MAY-81       2850
                    7782 CLARK      09-JUN-81       2450
                    7788 SCOTT      19-APR-87       3000
                    7839 KING       17-NOV-81       5000
                    7902 FORD       03-DEC-81       3000
              
              6 rows selected.
              
              Elapsed: 00:00:00.18
              satyaki>
              Due to the technical problem i cannot post the tested data...

              Regards.

              Satyaki De.