invalid cursor in 9i (but works on 11g) [message #520023] |
Thu, 18 August 2011 04:08 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Roger22
Messages: 98 Registered: April 2009 Location: Brasov, ROMANIA
|
Member |
|
|
I have:
create or replace function getCapatTransa2 return sys_Refcursor is
tmp sys_Refcursor;
error varchar2(1500);
begin
open tmp for select 'x' from dual;
return tmp;
end getCapatTransa2;
then
create or replace function get_capat_transa(p1 sys_refcursor, p2 number) return varchar2 is
cod varchar2(40);
begin
for i in 1..p2 loop
-- if p1%isopen then
--raise_application_error (-20667, cod);
fetch p1 into cod;
end loop;
return cod;
end;
and finally:
select get_capat_transa(getCapatTransa2, 1 ) cod
from dual
Why in 9i returns invalid cursor and in 11 returns 'x' ? Seems like in 9i the cursor p1 is not open, but why? i can't explain..
[Updated on: Thu, 18 August 2011 04:09] Report message to a moderator
|
|
|
|
|
|
|
|
|
|
|
|
Re: invalid cursor in 9i (but works on 11g) [message #520045 is a reply to message #520042] |
Thu, 18 August 2011 04:59 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Don't tell us not to ask why.
We need to know why you are trying to do what you are doing so that we can suggest an alternative that'll work.
As it stands it looks like you can't use a ref_cursor in a select in 9i like that.
If you really "need it in that manner and that's all" then you're just going to have to abandon using 9i aren't you?
What a stupid thing to say.
EDIT: typo
[Updated on: Thu, 18 August 2011 05:04] Report message to a moderator
|
|
|
|
|
|
|
|
|
|
|
Re: invalid cursor in 9i (but works on 11g) [message #520085 is a reply to message #520081] |
Thu, 18 August 2011 09:15 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
And it works:
SQL> declare var varchar2(10);
2 begin
3 var := get_capat_transa(getCapatTransa2, 1 );
4 end;
5 /
PL/SQL procedure successfully completed.
SQL>
But then we don't have 9i, that was on 10.2.0.5
However you should not be getting that error message. That error message should only happen if the first parameter in get_capat_transa is either IN OUT or OUT. Which it isn't according to the definitions above.
So either you're hitting a very weird oracle bug or you've done something that you haven't told us about.
|
|
|
|
|
|
|
|