collections / records / index-by, etc - long, sorry

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

mtab_ename
mtab_enum

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

TYPE emptabtype IS TABLE OF emp%ROWTYPE INDEX BY BINARY_INTEGER;

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
Oracle Database Administrator
Fairfield Resorts, Inc.
954-935-4117 Received on Thu Sep 06 2001 - 08:46:04 CDT