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

Lisa,
 

Shouldn't it be: <FONT face=Georgia
size=3>mtab_emp(i).ename ?
(Not mtab_emp.ename(i))
 

Igor Neyman, OCP DBAPerceptron, Inc.(734)414-4627<A href="mailto:ineyman_at_perceptron.com">ineyman_at_perceptron.com 

<BLOCKQUOTE
style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">

• Original Message ----- <DIV style="BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: black">From: <A title=lisa.koivu_at_efairfield.com href="mailto:lisa.koivu_at_efairfield.com">Koivu, Lisa To: <A title=ORACLE-L_at_fatcity.com href="mailto:ORACLE-L_at_fatcity.com">Multiple recipients of list ORACLE-L

  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 <FONT
  face=Georgia>mtab_enum
  and inserted values like so
  mtab_enum(idx) := var1; <FONT
  face=Georgia>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 - 10:34:10 CDT