----- Original Message -----
Sent: Thursday, September 06, 2001 10:36
AM
Subject: 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
