Home » SQL & PL/SQL » SQL & PL/SQL » Strange Invalid Cursor error with sys_refcursor (Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production With the Partitioning, OLAP and Data Mining options)
Strange Invalid Cursor error with sys_refcursor [message #358346] Mon, 10 November 2008 16:21 Go to next message
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 #358356 is a reply to message #358346] Mon, 10 November 2008 19:41 Go to previous messageGo to next message
rleishman
Messages: 3700
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Make the arguments IN OUT instead of just OUT. See if that helps.

Ross Leishman
Re: Strange Invalid Cursor error with sys_refcursor [message #358384 is a reply to message #358346] Tue, 11 November 2008 01:25 Go to previous messageGo to next message
Michel Cadot
Messages: 59118
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and use code tags.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Use SQL*Plus and copy and paste your session.

Regards
Michel
Re: Strange Invalid Cursor error with sys_refcursor [message #358465 is a reply to message #358356] Tue, 11 November 2008 08:20 Go to previous messageGo to next message
harishmk
Messages: 7
Registered: November 2008
Junior Member
Changed the parameter to 'in out', still the same error
Re: Strange Invalid Cursor error with sys_refcursor [message #358469 is a reply to message #358465] Tue, 11 November 2008 08:53 Go to previous messageGo to next message
Michel Cadot
Messages: 59118
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Tue, 11 November 2008 08:25
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and use code tags.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Use SQL*Plus and copy and paste your session.

Regards
Michel


Re: Strange Invalid Cursor error with sys_refcursor [message #358471 is a reply to message #358465] Tue, 11 November 2008 08:54 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
I believe this is what is happening in your case.
Quote:

Avoiding Errors with Cursor Variables
.....
If you assign an unopened cursor variable to another cursor variable, the second one remains invalid even after you open the first one.
.....


http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/sqloperations.htm#i7106

Regards

Raj

Re: Strange Invalid Cursor error with sys_refcursor [message #358474 is a reply to message #358465] Tue, 11 November 2008 08:57 Go to previous messageGo to next message
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
Value 34
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #358484 is a reply to message #358478] Tue, 11 November 2008 09:39 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Can you cut and paste an SQL*Plus session where you run the code I just posted?
Re: Strange Invalid Cursor error with sys_refcursor [message #358486 is a reply to message #358474] Tue, 11 November 2008 09:44 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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.
Re: Strange Invalid Cursor error with sys_refcursor [message #358776 is a reply to message #358678] Wed, 12 November 2008 08:15 Go to previous message
harishmk
Messages: 7
Registered: November 2008
Junior Member
i am using the work around that raj suggested,
have a temp cursor in proc2, pass that to proc1.
after calling proc1, assign temp cursor to the out param.
it works.
Previous Topic: insert /*+ append */ into UNIFORM_TEST
Next Topic: Different rows result between SELECT and UPDATE statments
Goto Forum:
  


Current Time: Wed Sep 17 20:54:51 CDT 2014

Total time taken to generate the page: 1.07503 seconds