pl/sql varrays

Hi all,

I have an object with its own functions and a varray type variable:


CREATE OR REPLACE TYPE idarray IS VARRAY(100000000) OF INTEGER;
/

CREATE OR REPLACE TYPE my_obj AS OBJECT
(
 ids idarray,
 ...
 STATIC FUNCTION ODCIIndexStart(sctx in out my_obj) RETURN NUMBER,
 MEMBER FUNCTION ODCIIndexFetch(self IN OUT my_obj) RETURN NUMBER
 ...
);
/


------------------------------------------------------------------------------------
I want to load a lot of integer into an idarray array inside the ODCIIndexStart function then give the array to the ODCIIndexFetch function as a parameter. As I realized if the size of the array is big it takes too much time while the array gets to the ODCIIndexFetch function.
My object's and the ODCIIndexStart and ODCIIndexFetch functions' interfaces cannot be changed thus I have to use the sctx and self parameters to "transport" the array.

Is there any solution that makes it possible to give the handler of the array (memory pointer, etc) as parameter not the whole array?

ps. I cannot use DML functions inside my code, thus I cannot write the array into a table.

code:
----------------------------

CREATE OR REPLACE TYPE BODY jc_idxtype_im IS

STATIC FUNCTION ODCIIndexStart(sctx in out my_obj) RETURN NUMBER IS
 ida idarray := idarray();
 BEGIN
   ...
     -- loading integers into the ida array
   ...
   sctx := my_obj(ida);
   RETURN 1;
 END;

MEMBER FUNCTION ODCIIndexFetch(self IN OUT my_obj) RETURN NUMBER IS
 ida idarray := idarray();
 BEGIN
   ida := self.ids;
 END;

END;
/

thanks,
gabor
gatyoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

vanmeerendonkCommented:
I've not a  lot of experience with objects/methods in oracle wasn't this possible using REF in combination with UTL_REF??

CREATE OR REPLACE TYPE my_obj AS OBJECT
(
 ids  REF idarray,

..
and within your  method
UTL_REF.SELECT_OBJECT(p_ref, ids)

other experts?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Chloe_LohCommented:
hi,

this is my first time to try to create an oracle database and can u guys give me the create database script? thank you

regards,
chloe
0
gatyoAuthor Commented:
thanks, looks good

In this case I have to call the my_obj() 's constructor with a REF parameter that reference to the 'ida' idarray.

How can I create a REF to a varray inside the ODCIIndexStart function?
How can I use that REF to add values to the array?

thanks

0
andrewstCommented:
You can use the NOCOPY option:

sctx in out NOCOPY my_obj
0
vanmeerendonkCommented:
For adding...
something like
UTL_REF.LOCK_OBJECT(p_ref, l_object);  --> locked and selected
.. alter l_object..
UTL_REF.UPDATE_OBJECT(p_ref,l_object); --> put it back

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.