Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> collections/plsql tables

collections/plsql tables

From: Bill Becker <>
Date: Thu, 06 Sep 2001 08:13:41 -0700
Message-ID: <>

I am also interested in this, as my current project sounds quite similar (developing an etl load process for a small Oracle warehouse using pl/sql). I don't have any real help to offer, only that I ran into the same limitation; I could not get FORALL and BULK COLLECT to work with composite record structures. I created several pl/sql tables containing records with a %ROWTYPE identical to an Oracle table, loaded and transformed the records, and tried several variations of FORALL using a record structure. What I would like to do is something like

FORALL i IN mtab_records.FIRST..mtab_records.LAST   INSERT INTO emp VALUES mtab_records(i);

where mtab_records is a pl/sql table containing composite records; the record would have a %ROWTYPE of the target table, and the insert command would match up the record.fields with the table.columns, since they are identical. I would also like to do somewhat the reverse operation using BULK COLLECT. However, I have also concluded that FORALL and BULK COLLECT are very 1-dimensional, and that single dimension cannot be a composite type. If anyone knows different, would appreciate your comments as well.


Good morning everyone,

well I finally have something to work on. Not being one to whip out shoddy code, I want to write my load scripts utilizing pl/sql tables and caching as much as I can, along with utilizing FORALL and BULK COLLECT.

The last time I did this, I was creating table rows in pl/sql INDEX-BY tables. I had one pl/sql table for each column in the target table (that I was going to insert modified rows to) and it worked fine, very fast in fact. However, it was an awful mess because I ended up maintaining many many INDEX-BY tables with one index to refer to each record.

What I'm talking about is this

table in the db is emp : enum number, ename varchar

To represent this table in memory and assemble the records I created the following index-by tables at the module (package) level


and inserted values like so

mtab_enum(idx) := var1;
mtab_ename(idx) := var2;

and when it came time to insert, this is what I did

FORALL i IN mtab_enum.FIRST..mtab_enum.LAST   INSERT INTO emp (enum, ename) VALUES mtab_enum(i), mtab_ename(i);

My question is, is there a way I can have one object that represents the structure of the entire emp table? I tried this


mtab_emp emptabtype;

But this doesn't seem to work. I can't pull the values out (var := mtab_emp.ename(i)). I also don't want to use varrays just because I have to explicitly set the size.

I also want to be able to use BULK COLLECT and FORALL. Otherwise this kind of stuff is a waste of time. I then read in the documentation that "Collections can have only one dimension and must be indexed by integers". It sounds like what I want to do isn't possible.

Any suggestions or comments are appreciated. Thanks

Lisa Koivu

Please see the official ORACLE-L FAQ:
Author: Bill Becker

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Thu Sep 06 2001 - 10:13:41 CDT

Original text of this message