Return values pt. 2

[TyzA]

Hi again,

The previous one was a bit foggy, so here are more details.

I'm building a memory table inside my stored proc.
Is it possible to return all data in this memory table to java?

I think it is possible by creating the memory table, then use a cursor to fetch all the data and return this cursor as resultset to java, but I not sure of it.

Hope you guys can help me.

Thanks a lot.

Tijs Programming is like sex: one mistake and you have to support it for the rest of your life.

 

[BilliaUK]

TyzA

As long as you are happy using ref cursors (to pass back to JAVA) and creating SQL nested table types on the database, then this is simple. I don't use JAVA clients, so will show you a very simple demo - you can adapt it to your needs...

--
-- Create SQL types...
--
create type myObjectType as object
( object_name varchar2(30)
, object_type varchar2(18)
);
/

create type myTableType as table of myObjectType;
/

--
-- Package to globally declare a ref cursor type...
--
create package myTypes as
type myRefCursorType is ref cursor;
end myTypes;
/

--
-- Your function to return ref cursor to whatever requires
-- it (in your case JAVA client, in my case SQL*Plus...
--
create function myFunction return myTypes.myRefCursorType is
my_objects myTableType;
rc myTypes.myRefCursorType;
begin

/* Load the array ... */
select myObjectType(object_name, object_type)
bulk collect into my_objects
from user_objects;

/* Open a ref cursor to start fetching the data from the array */
open rc for select t.object_name, t.object_type
from table(cast(my_objects as myTableType)) t;

/* Return the ref cursor to the client... */
return rc;

end;
/

Now I haven't got a database in front of me so this was all written straight into the web-form, so there might be syntax / typos. But you should get the idea. Just have your client ready to receive ref cursors from the function.

Regards

Adrian