Home » SQL & PL/SQL » SQL & PL/SQL » cursor to table in anonymous block (Oracle 9i)
cursor to table in anonymous block [message #316663] Sun, 27 April 2008 06:22 Go to next message
dcoales
Messages: 3
Registered: April 2008
Junior Member
Hi I have a requirement to run an anonymous plsql block and return a cursor to my jdbc.

I have no problem doing this except in the case where I define a table type in the plsql and try to return a cursor to that. A very simple example might be:

DECLARE
TYPE myCur IS REF CURSOR;
TYPE numberList is TABLE OF NUMBER INDEX BY BINARY_INTEGER;
myNumberList numberList;
FUNCTION getNumbers RETURN myCur
AS streamCur myCur;
BEGIN
myNumberList(1) := 1;
OPEN streamCur FOR select * from table(cast(myNumberList as numberList));
RETURN streamCur;
END;
BEGIN
? := getNumbers;
END;

However if I try to run this I get an "invalid datatype" message.
It would seem that the numberList type isn't valid unless I first create it outside the anonymous block in a package somewhere but I don't want to do this. Does anyone know how I can return a cursor to a PL/SQL table using only an anonymouse block without any create or replace statements.
Re: cursor to table in anonymous block [message #316665 is a reply to message #316663] Sun, 27 April 2008 07:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is the basic notion of scope in any programmatic language.
Your type is defined and only known in your PL/SQL block.

Regards
Michel
Re: cursor to table in anonymous block [message #316667 is a reply to message #316665] Sun, 27 April 2008 07:40 Go to previous messageGo to next message
dcoales
Messages: 3
Registered: April 2008
Junior Member
So is there no alternative method to achieve the same end or am I simply attempting the impossible.
Re: cursor to table in anonymous block [message #316670 is a reply to message #316667] Sun, 27 April 2008 08:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You already know the answer.
The scope of the type must be larger than the PL/SQL block and so the type must be defined outside the block.

Regards
Michel
Re: cursor to table in anonymous block [message #316854 is a reply to message #316670] Mon, 28 April 2008 10:11 Go to previous message
dcoales
Messages: 3
Registered: April 2008
Junior Member
Oh well Sad
Thanks for your help anyway.
Previous Topic: sqlerrm,sqlcode
Next Topic: problem with implementing a scenario in sql due to group by limitation
Goto Forum:
  


Current Time: Tue Dec 06 10:31:52 CST 2016

Total time taken to generate the page: 0.17972 seconds