gatyo
asked on
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
For adding...
something like
UTL_REF.LOCK_OBJECT(p_ref, l_object); --> locked and selected
.. alter l_object..
UTL_REF.UPDATE_OBJECT(p_re f,l_object ); --> put it back
something like
UTL_REF.LOCK_OBJECT(p_ref,
.. alter l_object..
UTL_REF.UPDATE_OBJECT(p_re
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