This discussion is archived
4 Replies Latest reply: Aug 14, 2008 9:14 AM by Satyaki_De RSS

Passing cursor as parameter

582561 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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.