Re: HOWTO: STORED PROCEDURE RETURNING MANY ROWS

From: Andreas Mosmann <mosmann_at_expires-30-06-2011.news-group.org>
Date: Mon, 20 Jun 2011 21:54:29 +0200
Message-ID: <1308599669.8_at_user.newsoffice.de>

Gunter Herrmann schrieb am 20.06.2011 in <4dff5c63$0$7625$9b4e6d93_at_newsspool1.arcor-online.net>:

> Hi!

> Andreas Mosmann wrote:

>> Gunter Herrmann schrieb am 17.06.2011 in

>>> You will need to create database types for the row type of the function.
>>> Additionally
>>> you will create a table type as TABLE OF<row type from above>.

>> My source (simplified)
>> create package MyPack as
>> ********************************************************/
>> type T_R_RETURN is record (
>> 	   Datum Date, Operation varchar(256), Zahl1 number, Zahl2

>> number);

> As I said above: DATABASE TYPE

> CREATE OR REPLACE TYPE T_R_RETURN AS OBJECT (
> ID number, ...
> );

>>
>> type T_REF_TABLE is table of T_R_RETURN index by binary_integer;

> This type as well as a DATABASE TYPE

> CREATE OR REPLACE TYPE T_REF_TABLE IS
> TABLE OF T_R_RETURN;

> I suggest you have a look at the examples in some recent PL/SQL books that
> cover at least 9i. Both Steven Feuerstein and Scott Urman have examples.

> Gunter in Orlando

You helped me a lot.
The code now compiles and runs. My new problem is error 6531/6530 + 6512

Code-Fragment is

	ResultTable:=T_REF_TABLE(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
	ResultTable(1).ID:=1;

T_REF_TABLE has 9 Elements.
I tried ResultTable.Extend but it did not work too. I develop software for years, I know that dynamic vars have to be initialized/constructed. But in that case: how? And who destructes the var?
If you can tell me some new words I could search for (or a link to online examples) - it would be nice. I do not have books concerning this issue, I'm afraid.

Thank you
Andreas

btw: I hoped to encapsulate all - types and procedures/function in a package but it seems to be impossible to do this. If I do it direct without package your way works. No prob but confusing.

-- 
wenn email, dann AndreasMosmann <bei> web <punkt> de

Received on Mon Jun 20 2011 - 14:54:29 CDT