| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: collections / records / index-by, etc - long, sorry
Thanks Prakash,
but can I use FORALL with this object? I don't think so. Correct me if I'm wrong.
Thank you
Lisa Koivu
Ft. Lauderdale, FL, USA
-----Original Message-----
From: Bala, Prakash [SMTP:prakash.bala_at_cingular.com] Sent: Thursday, September 06, 2001 5:36 PM To: Multiple recipients of list ORACLE-LSubject: RE: collections / records / index-by, etc - long, sorry
Lisa,
How about this:
declare
EmpRec Emp%ROWTYPE;
type EmpTable is table of EmpRec%type index by binary_integer;
emp EmpTable;
begin
emp(1).ename := 'xx';
emp(1).ssn := 896767097;
emp(2).ename := 'yy';
end;
Prakash
-----Original Message-----
Sent: Thursday, September 06, 2001 10:36 AM
To: Multiple recipients of list ORACLE-L
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
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bala, PrakashReceived on Mon Sep 10 2001 - 07:16:37 CDT
INET: prakash.bala_at_cingular.com
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: ListGuru_at_fatcity.com (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).
 |
 |