Re: PL/SQL: What is the maxiimum size of ARRAY OF RECORD

Claus (clkhm_at_hotmail.com) wrote:
: Hi all,

: Lets say I have the following PL/SQL block:
: (I know the code is not correct, its just to illustrate)

: DECLARE
: CURSOR c IS select * from EMP;

: TYPE emprecs IS ARRAY OF EMP%TYPE INDEX BY BINARY_INTEGER;

: myrecs emprecs;

: BEGIN
: OPEN c;
: FETCH c INTO myrecs;
: CLOSE c;

: FOR myrecs.first TO myrecs.last LOOP
: do something
: END LOOP;
: END;

: Now the questions:

: What is the memory limitation on the size of the "myrecs" array?

: Which init.ora parameter controls the maximum size?

: Where is "myrecs" actually allocated, - in the SGA or PGA?

: What exception will I get if the maximum size is exceeded?

: Hope you can help me out.

Also, look into bulk load statements. You can work with a predetermined number of rows at a time.

BUT the term "bulk load" is not quote right.

I think the sql uses words like BULK COLLECT and FORALL - you'll have to look them up.

Experiment with the nuber of rows to work with at a time. I found, when I used them, that loading upto about 10,000 rows at a time made a big difference, but after that no difference. The number presumably varies based on many factors, but the point is that the number of rows can be controlled, and also tuned to maximize speed while still minimizing memory. Received on Tue Dec 02 2003 - 23:01:05 CET