Strange Invalid Cursor error with sys_refcursor [message #358346] |
Mon, 10 November 2008 16:21 |
harishmk
Messages: 7 Registered: November 2008
|
Junior Member |
|
|
I've 3 procedures. proc3 calls proc2 calls proc1
proc3 passes a sys_refcursor as an out parameter to proc2
proc2 passes that to proc1
proc1 opens the cursor with a select
proc3 simply closes the out cursor
i am getting "ORA-01001: invalid cursor" while closing the cursor.
any idea?
-- PROC1
CREATE OR REPLACE
PROCEDURE TEST_PROC1
(
oCur OUT SYS_REFCURSOR
) AS
BEGIN
OPEN oCur FOR SELECT entity_id FROM entity;
END TEST_PROC1;
-- PROC2
CREATE OR REPLACE
PROCEDURE TEST_PROC2
(
oCur OUT SYS_REFCURSOR
) AS
BEGIN
test_proc1(oCur);
END TEST_PROC2;
-- PROC3
CREATE OR REPLACE
PROCEDURE TEST_PROC3 AS
oCur SYS_REFCURSOR;
BEGIN
test_proc2(oCur);
CLOSE oCur; -- Invalid cursor error here
END TEST_PROC3;
|
|
|
|
|
|
|
|
Re: Strange Invalid Cursor error with sys_refcursor [message #358474 is a reply to message #358465] |
Tue, 11 November 2008 08:57 |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
I don't get what you get.
I run this:create table entity (entity_id number);
insert into entity values (34);
commit;
CREATE OR REPLACE PROCEDURE TEST_PROC1 (oCur OUT SYS_REFCURSOR) AS
BEGIN
OPEN oCur FOR SELECT entity_id FROM entity;
END TEST_PROC1;;
/
-- PROC2
CREATE OR REPLACE PROCEDURE TEST_PROC2 (oCur OUT SYS_REFCURSOR) AS
BEGIN
test_proc1(oCur);
END TEST_PROC2;;
/
-- PROC3
CREATE OR REPLACE PROCEDURE TEST_PROC3 AS
oCur SYS_REFCURSOR;
v_num number;
BEGIN
test_proc2(oCur);
fetch oCur into v_num;
CLOSE oCur; -- Invalid cursor error here
dbms_output.put_line('Value '||v_num);
END TEST_PROC3;;
/
begin
test_proc3;
end;
/
and get the line back.
I'm running on 10.2.0.4.0.
|
|
|
Re: Strange Invalid Cursor error with sys_refcursor [message #358477 is a reply to message #358471] |
Tue, 11 November 2008 09:08 |
harishmk
Messages: 7 Registered: November 2008
|
Junior Member |
|
|
Thanks a lot Raj! This indeed was the issue.
here is how i fixed it.
proc3 -> proc2 -> proc1
in proc2, i declared a temp cursor, and passed
that to proc1.
after calling proc1, proc2 now assigns the temp cursor
to the out cursor.
it works. but is this the best way?
|
|
|
Re: Strange Invalid Cursor error with sys_refcursor [message #358478 is a reply to message #358474] |
Tue, 11 November 2008 09:21 |
harishmk
Messages: 7 Registered: November 2008
|
Junior Member |
|
|
JRowBottom,
Not sure how is this working for you.
except that I am using a slightly older version:
SQL*Plus: Release 10.2.0.3.0 - Production on Tue Nov 11 10:18:42 2008
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
|
|
|
|
Re: Strange Invalid Cursor error with sys_refcursor [message #358486 is a reply to message #358474] |
Tue, 11 November 2008 09:44 |
S.Rajaram
Messages: 1027 Registered: October 2006 Location: United Kingdom
|
Senior Member |
|
|
Well interestingly the same piece of code is producing ora-1001 in 10.2.0.3. If I changed the proc2 as follows, it works in 10.2.0.3.
create or replace procedure test_proc2 (rc out sys_refcursor)
is
temp_rc sys_refcursor;
begin
test_proc1(temp_rc);
rc := temp_rc;
end;
Having said that if I try the same piece of code in 11.1.0.6 I am hitting the following BUG 7174888 (I have not enclosed the query in quotes). Whereas If I try to enclose the query in quotes it is working.
@OP, Unfortunately if you are in 10.2.0.3 (like me) I cannot think of any other alternative.
I think I have interpretted the documentation incorrectly.
Regards
Raj
|
|
|
Re: Strange Invalid Cursor error with sys_refcursor [message #358489 is a reply to message #358484] |
Tue, 11 November 2008 09:53 |
harishmk
Messages: 7 Registered: November 2008
|
Junior Member |
|
|
JRowBottom,
I tried your modified proc3, and it gives the same error.
also: You have two semi-colons at the end of each proc, sqlplus was giving an error on that.
SQL> CREATE OR REPLACE PROCEDURE TEST_PROC3 AS
oCur SYS_REFCURSOR;
v_num number;
BEGIN
test_proc2(oCur);
fetch oCur into v_num;
CLOSE oCur; -- Invalid cursor error here
dbms_output.put_line('Value '||v_num);
END TEST_PROC3; 2 3 4 5 6 7 8 9 10
11 /
Procedure created.
SQL> begin
test_proc3;
end;
/ 2 3 4
begin
*
ERROR at line 1:
ORA-06504: PL/SQL: Return types of Result Set variables or query do not match
ORA-06512: at "PERMSVC.TEST_PROC3", line 6
ORA-06512: at line 2
|
|
|
Re: Strange Invalid Cursor error with sys_refcursor [message #358670 is a reply to message #358489] |
Wed, 12 November 2008 02:11 |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
The two semi-colons thing is a workround you need in SQL Developer if you try to create procedures in a SQL Editor window.
The example you pos shows you getting a completely different error - I don't# see an ORA-1001 in your last post.
I'm reasonably sure that the error you get (Ora-6504) is becuase the entity_id column in your entity table isn't a Number tyoe, ad doesn't contain numeric data.
I'd have used your entity table rather than create my own, only you didn't bother to give us a create table script for it....
|
|
|
Re: Strange Invalid Cursor error with sys_refcursor [message #358678 is a reply to message #358670] |
Wed, 12 November 2008 02:27 |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Hmm - seems I'm wrong.
I've got 10.2.0.3 up and running, and I get the same error.
You can make everything work perfectly by removing Proc2 - make Proc3 refer directly to Proc1.
Additionally, if you define your own weak ref cursor type like this:create or replace package pkg_types as
type rc is ref cursor;
end;
/ and use that insteadof sys_refcursor, then things work.
Have you raised this with Oracle yet? They amy have a workaround.
|
|
|
|