Solved

pl/sql varrays

Posted on 2003-11-05
7
983 Views
Last Modified: 2011-09-20
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
0
Comment
Question by:gatyo
7 Comments
 
LVL 2

Accepted Solution

by:
vanmeerendonk earned 63 total points
ID: 9687294
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
 

Expert Comment

by:Chloe_Loh
ID: 9687426
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
 

Author Comment

by:gatyo
ID: 9687450
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
 
LVL 15

Assisted Solution

by:andrewst
andrewst earned 62 total points
ID: 9687543
You can use the NOCOPY option:

sctx in out NOCOPY my_obj
0
 
LVL 2

Expert Comment

by:vanmeerendonk
ID: 9693010
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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Oracle Subquery bad Join 11 59
alter database link to change the password 2 52
Oracle RAC 12c 8 60
Migrate Oracle Database from ASM to Non-ASM on a Windows server. 1 33
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now