Home » SQL & PL/SQL » SQL & PL/SQL » ref cursors and IN OUT parameter mode
ref cursors and IN OUT parameter mode [message #125894] Wed, 29 June 2005 15:51 Go to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
As discussed in another thread, I was under the impression that you had to declare a ref cursor parameter IN OUT, as opposed to just OUT, in a procedure. Barbara pointed out that this may be version specific. I spent some time searching, but must not be using the correct search terms as I can't find an answer.

So I created a test that uses various modes, and attempts to identify the situations that would fail. But the problem is that I can't seem to find a combination that fails at either compile time or run time.

1) Can anyone see what I'm doing wrong? Why is this working?

2) Would someone mind running this on a different version? I'm running 10.1.0.4 on WinXP. Perhaps someone out there has a 9i and/or 8i version to test this on?

I've included both the output of the run, as well as the code only to make it easier to copy and paste and run yourself.

First the code and output combo:

MYDBA@ORCL > start refcursor_test;
MYDBA@ORCL > -- refcursor_test.sql by Scot Martin
MYDBA@ORCL > -- 10.1.0.4 29-JUN-2005
MYDBA@ORCL >
MYDBA@ORCL > -- Testing the passing of reference cursors via the old method,
MYDBA@ORCL > -- which used a type declaration of ref cursor, and the new mthod,
MYDBA@ORCL > -- which uses the built in sys_refcursor type.
MYDBA@ORCL >
MYDBA@ORCL > create table test(a) as select rownum from all_objects where rownum <= 3;

Table created.

MYDBA@ORCL > select * from test;

         A
----------
         1
         2
         3

3 rows selected.

MYDBA@ORCL >
MYDBA@ORCL > create or replace package refcursor_test as
  2
  3  type rctype is ref cursor;
  4
  5  procedure displayold(c in rctype);
  6  procedure displaynew(c in sys_refcursor);
  7
  8  procedure out_proc_old(c out rctype);
  9  procedure out_proc_new(c out sys_refcursor);
 10  procedure inout_proc_old(c in out rctype);
 11  procedure inout_proc_new(c in out sys_refcursor);
 12
 13  end refcursor_test;
 14  /

Package created.

MYDBA@ORCL > show errors
No errors.
MYDBA@ORCL >
MYDBA@ORCL > create or replace package body refcursor_test as
  2
  3  procedure displayold(c in rctype) is
  4      i number;
  5  begin
  6      loop
  7          fetch c into i;
  8          exit when c%notfound;
  9          dbms_output.put(i);
 10      end loop;
 11      dbms_output.put_line('.');
 12  end;
 13
 14  procedure displaynew(c in sys_refcursor) is
 15      i number;
 16  begin
 17      loop
 18          fetch c into i;
 19          exit when c%notfound;
 20          dbms_output.put(i);
 21      end loop;
 22      dbms_output.put_line('.');
 23  end;
 24
 25  procedure out_proc_old(c out rctype) is
 26  begin
 27      open c for select * from test;
 28  end;
 29
 30  procedure out_proc_new(c out sys_refcursor) is
 31  begin
 32      open c for select * from test;
 33  end;
 34
 35  procedure inout_proc_old(c in out rctype) is
 36  begin
 37      open c for select * from test;
 38  end;
 39
 40  procedure inout_proc_new(c in out sys_refcursor) is
 41  begin
 42      open c for select * from test;
 43  end;
 44
 45  end refcursor_test;
 46  /

Package body created.

MYDBA@ORCL > show errors
No errors.
MYDBA@ORCL >
MYDBA@ORCL > var myrc refcursor;
MYDBA@ORCL >
MYDBA@ORCL > exec refcursor_test.out_proc_old(:myrc);

PL/SQL procedure successfully completed.

MYDBA@ORCL > print myrc;

         A
----------
         1
         2
         3

3 rows selected.

MYDBA@ORCL >
MYDBA@ORCL > insert into test values(4);

1 row created.

MYDBA@ORCL > commit;

Commit complete.

MYDBA@ORCL > exec refcursor_test.out_proc_new(:myrc);

PL/SQL procedure successfully completed.

MYDBA@ORCL > print myrc;

         A
----------
         1
         2
         3
         4

4 rows selected.

MYDBA@ORCL >
MYDBA@ORCL > insert into test values(5);

1 row created.

MYDBA@ORCL > commit;

Commit complete.

MYDBA@ORCL > exec refcursor_test.inout_proc_old(:myrc);

PL/SQL procedure successfully completed.

MYDBA@ORCL > print myrc;

         A
----------
         1
         2
         3
         4
         5

5 rows selected.

MYDBA@ORCL >
MYDBA@ORCL > insert into test values(6);

1 row created.

MYDBA@ORCL > commit;

Commit complete.

MYDBA@ORCL > exec refcursor_test.inout_proc_new(:myrc);

PL/SQL procedure successfully completed.

MYDBA@ORCL > print myrc;

         A
----------
         1
         2
         3
         4
         5
         6

6 rows selected.

MYDBA@ORCL >
MYDBA@ORCL > truncate table test;

Table truncated.

MYDBA@ORCL > insert into test select rownum from all_objects where rownum <= 3;

3 rows created.

MYDBA@ORCL > commit;

Commit complete.

MYDBA@ORCL >
MYDBA@ORCL > declare
  2      mynewrc sys_refcursor;
  3  begin
  4      refcursor_test.out_proc_old(mynewrc);
  5      refcursor_test.displaynew(mynewrc);
  6
  7      insert into test values (4);
  8      commit;
  9      refcursor_test.out_proc_new(mynewrc);
 10      refcursor_test.displaynew(mynewrc);
 11
 12      insert into test values (5);
 13      commit;
 14      refcursor_test.inout_proc_old(mynewrc);
 15      refcursor_test.displaynew(mynewrc);
 16
 17      insert into test values (6);
 18      commit;
 19      refcursor_test.inout_proc_new(mynewrc);
 20      refcursor_test.displaynew(mynewrc);
 21  end;
 22  /
123.
1234.
12345.
123456.

PL/SQL procedure successfully completed.

MYDBA@ORCL >
MYDBA@ORCL > truncate table test;

Table truncated.

MYDBA@ORCL > insert into test select rownum from all_objects where rownum <= 3;

3 rows created.

MYDBA@ORCL > commit;

Commit complete.

MYDBA@ORCL >
MYDBA@ORCL > declare
  2      type myoldrctype is ref cursor;
  3      myoldrc myoldrctype;
  4  begin
  5      refcursor_test.out_proc_old(myoldrc);
  6      refcursor_test.displayold(myoldrc);
  7
  8      insert into test values (4);
  9      commit;
 10      refcursor_test.out_proc_new(myoldrc);
 11      refcursor_test.displayold(myoldrc);
 12
 13      insert into test values (5);
 14      commit;
 15      refcursor_test.inout_proc_old(myoldrc);
 16      refcursor_test.displayold(myoldrc);
 17
 18      insert into test values (6);
 19      commit;
 20      refcursor_test.inout_proc_new(myoldrc);
 21      refcursor_test.displayold(myoldrc);
 22  end;
 23  /
123.
1234.
12345.
123456.

PL/SQL procedure successfully completed.

MYDBA@ORCL >
MYDBA@ORCL > drop package refcursor_test;

Package dropped.

MYDBA@ORCL > drop table test;

Table dropped.


And here is just the code:

-- refcursor_test.sql by Scot Martin
-- 10.1.0.4 29-JUN-2005

-- Testing the passing of reference cursors via the old method,
-- which used a type declaration of ref cursor, and the new mthod,
-- which uses the built in sys_refcursor type.

create table test(a) as select rownum from all_objects where rownum <= 3;
select * from test;

create or replace package refcursor_test as

type rctype is ref cursor;

procedure displayold(c in rctype);
procedure displaynew(c in sys_refcursor);

procedure out_proc_old(c out rctype);
procedure out_proc_new(c out sys_refcursor);
procedure inout_proc_old(c in out rctype);
procedure inout_proc_new(c in out sys_refcursor);

end refcursor_test;
/
show errors

create or replace package body refcursor_test as

procedure displayold(c in rctype) is
    i number;
begin
    loop
        fetch c into i;
        exit when c%notfound;
        dbms_output.put(i);
    end loop;
    dbms_output.put_line('.');
end;

procedure displaynew(c in sys_refcursor) is
    i number;
begin
    loop
        fetch c into i;
        exit when c%notfound;
        dbms_output.put(i);
    end loop;
    dbms_output.put_line('.');
end;

procedure out_proc_old(c out rctype) is
begin
    open c for select * from test;
end;

procedure out_proc_new(c out sys_refcursor) is
begin
    open c for select * from test;
end;

procedure inout_proc_old(c in out rctype) is
begin
    open c for select * from test;
end;

procedure inout_proc_new(c in out sys_refcursor) is
begin
    open c for select * from test;
end;

end refcursor_test;
/
show errors

var myrc refcursor;

exec refcursor_test.out_proc_old(:myrc);
print myrc;

insert into test values(4);
commit;
exec refcursor_test.out_proc_new(:myrc);
print myrc;

insert into test values(5);
commit;
exec refcursor_test.inout_proc_old(:myrc);
print myrc;

insert into test values(6);
commit;
exec refcursor_test.inout_proc_new(:myrc);
print myrc;

truncate table test;
insert into test select rownum from all_objects where rownum <= 3;
commit;

declare
    mynewrc sys_refcursor;
begin
    refcursor_test.out_proc_old(mynewrc);
    refcursor_test.displaynew(mynewrc);

    insert into test values (4);
    commit;
    refcursor_test.out_proc_new(mynewrc);
    refcursor_test.displaynew(mynewrc);

    insert into test values (5);
    commit;
    refcursor_test.inout_proc_old(mynewrc);
    refcursor_test.displaynew(mynewrc);
    
    insert into test values (6);
    commit;
    refcursor_test.inout_proc_new(mynewrc);
    refcursor_test.displaynew(mynewrc);
end;
/

truncate table test;
insert into test select rownum from all_objects where rownum <= 3;
commit;

declare
    type myoldrctype is ref cursor;
    myoldrc myoldrctype;
begin
    refcursor_test.out_proc_old(myoldrc);
    refcursor_test.displayold(myoldrc);

    insert into test values (4);
    commit;
    refcursor_test.out_proc_new(myoldrc);
    refcursor_test.displayold(myoldrc);

    insert into test values (5);
    commit;
    refcursor_test.inout_proc_old(myoldrc);
    refcursor_test.displayold(myoldrc);

    insert into test values (6);
    commit;
    refcursor_test.inout_proc_new(myoldrc);
    refcursor_test.displayold(myoldrc);
end;
/

drop package refcursor_test;
drop table test;

Re: ref cursors and IN OUT parameter mode [message #125902 is a reply to message #125894] Wed, 29 June 2005 16:36 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
I have only used an OUT parameter for ref cursors since 8i. I believe I read somewhere that IN OUT was required back in the 7.x days.

sql>select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
PL/SQL Release 9.2.0.6.0 - Production
CORE    9.2.0.6.0       Production
TNS for Solaris: Version 9.2.0.6.0 - Production
NLSRTL Version 9.2.0.6.0 - Production

5 rows selected.

sql>create table test(a) as select rownum from all_objects where rownum <= 3;

Table created.

sql>select * from test;

        A
---------
        1
        2
        3

3 rows selected.

sql>create or replace package refcursor_test as
  2  
  3  type rctype is ref cursor;
  4  
  5  procedure displayold(c in rctype);
  6  procedure displaynew(c in sys_refcursor);
  7  
  8  procedure out_proc_old(c out rctype);
  9  procedure out_proc_new(c out sys_refcursor);
 10  procedure inout_proc_old(c in out rctype);
 11  procedure inout_proc_new(c in out sys_refcursor);
 12  
 13  end refcursor_test;
 14  /

Package created.

sql>show errors
No errors.
sql>create or replace package body refcursor_test as
  2  
  3  procedure displayold(c in rctype) is
  4      i number;
  5  begin
  6      loop
  7          fetch c into i;
  8          exit when c%notfound;
  9          dbms_output.put(i);
 10      end loop;
 11      dbms_output.put_line('.');
 12  end;
 13  
 14  procedure displaynew(c in sys_refcursor) is
 15      i number;
 16  begin
 17      loop
 18          fetch c into i;
 19          exit when c%notfound;
 20          dbms_output.put(i);
 21      end loop;
 22      dbms_output.put_line('.');
 23  end;
 24  
 25  procedure out_proc_old(c out rctype) is
 26  begin
 27      open c for select * from test;
 28  end;
 29  
 30  procedure out_proc_new(c out sys_refcursor) is
 31  begin
 32      open c for select * from test;
 33  end;
 34  
 35  procedure inout_proc_old(c in out rctype) is
 36  begin
 37      open c for select * from test;
 38  end;
 39  
 40  procedure inout_proc_new(c in out sys_refcursor) is
 41  begin
 42      open c for select * from test;
 43  end;
 44  
 45  end refcursor_test;
 46  /

Package body created.

sql>show errors
No errors.
sql>
sql>set autoprint on
sql>var myrc refcursor;
sql>exec refcursor_test.out_proc_old(:myrc);

PL/SQL procedure successfully completed.


        A
---------
        1
        2
        3

3 rows selected.

sql>insert into test values(4);

1 row created.

sql>commit;

Commit complete.

sql>exec refcursor_test.out_proc_new(:myrc);

PL/SQL procedure successfully completed.


        A
---------
        1
        2
        3
        4

4 rows selected.

sql>insert into test values(5);

1 row created.

sql>commit;

Commit complete.

sql>exec refcursor_test.inout_proc_old(:myrc);

PL/SQL procedure successfully completed.


        A
---------
        1
        2
        3
        4
        5

5 rows selected.

sql>insert into test values(6);

1 row created.

sql>commit;

Commit complete.

sql>exec refcursor_test.inout_proc_new(:myrc);

PL/SQL procedure successfully completed.


        A
---------
        1
        2
        3
        4
        5
        6

6 rows selected.

sql>truncate table test;

Table truncated.

sql>insert into test select rownum from all_objects where rownum <= 3;

3 rows created.

sql>commit;

Commit complete.

sql>declare
  2      mynewrc sys_refcursor;
  3  begin
  4      refcursor_test.out_proc_old(mynewrc);
  5      refcursor_test.displaynew(mynewrc);
  6  
  7      insert into test values (4);
  8      commit;
  9      refcursor_test.out_proc_new(mynewrc);
 10      refcursor_test.displaynew(mynewrc);
 11  
 12      insert into test values (5);
 13      commit;
 14      refcursor_test.inout_proc_old(mynewrc);
 15      refcursor_test.displaynew(mynewrc);
 16      
 17      insert into test values (6);
 18      commit;
 19      refcursor_test.inout_proc_new(mynewrc);
 20      refcursor_test.displaynew(mynewrc);
 21  end;
 22  /
123.
1234.
12345.
123456.

PL/SQL procedure successfully completed.

sql>truncate table test;

Table truncated.

sql>insert into test select rownum from all_objects where rownum <= 3;

3 rows created.

sql>commit;

Commit complete.

sql>declare
  2      type myoldrctype is ref cursor;
  3      myoldrc myoldrctype;
  4  begin
  5      refcursor_test.out_proc_old(myoldrc);
  6      refcursor_test.displayold(myoldrc);
  7  
  8      insert into test values (4);
  9      commit;
 10      refcursor_test.out_proc_new(myoldrc);
 11      refcursor_test.displayold(myoldrc);
 12  
 13      insert into test values (5);
 14      commit;
 15      refcursor_test.inout_proc_old(myoldrc);
 16      refcursor_test.displayold(myoldrc);
 17  
 18      insert into test values (6);
 19      commit;
 20      refcursor_test.inout_proc_new(myoldrc);
 21      refcursor_test.displayold(myoldrc);
 22  end;
 23  /
123.
1234.
12345.
123456.

PL/SQL procedure successfully completed.

sql>drop package refcursor_test;

Package dropped.

sql>drop table test;

Table dropped.
Re: ref cursors and IN OUT parameter mode [message #125903 is a reply to message #125894] Wed, 29 June 2005 16:37 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
I have a version of 8i (8.1.7.4). I tried to run this, but I think I rememeber Barbara saying that sys_refcursor was new in 9i, and sure enough, I could not compile the package spec.

LINE/COL ERROR
-------- ------------------------------------------------------
4/5 PL/SQL: Declaration ignored
4/31 PLS-00201: identifier 'SYS_REFCURSOR' must be declared
6/5 PL/SQL: Declaration ignored
6/34 PLS-00201: identifier 'SYS_REFCURSOR' must be declared
8/4 PL/SQL: Declaration ignored
8/38 PLS-00201: identifier 'SYS_REFCURSOR' must be declared

Hope this helps you out a little.

[Updated on: Wed, 29 June 2005 16:40]

Report message to a moderator

Re: ref cursors and IN OUT parameter mode [message #126211 is a reply to message #125894] Fri, 01 July 2005 09:51 Go to previous message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
I guess what surprised me was not so much that sys_refcursor, the "new way" would work with just an out, but that also the "old way" worked as well, at least from 9iR2 on. Even the 10g doc examples from plsql guide show examples with in out, but there is no mention of any particular requirements.

Thanks Todd and joy_division for your help.
Previous Topic: Can we extract data from two different distant oracle databases in the same procedure
Next Topic: PL/SQL script not loading over 9000 records into table
Goto Forum:
  


Current Time: Mon Aug 18 23:05:13 CDT 2025