ref cursors and IN OUT parameter mode [message #125894] |
Wed, 29 June 2005 15:51  |
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   |
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   |
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  |
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.
|
|
|