From ineyman@perceptron.com Thu, 06 Sep 2001 08:34:10 -0700
From: "Igor Neyman" <ineyman@perceptron.com>
Date: Thu, 06 Sep 2001 08:34:10 -0700
Subject: Re: collections / records / index-by, etc - long, sorry
Message-ID: <F001.00385414.20010906080638@fatcity.com>
MIME-Version: 1.0
Content-Type: text/plain
Title: 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@perceptron.com">ineyman@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@efairfield.com 
  href="mailto:lisa.koivu@efairfield.com">Koivu, Lisa 
  To: <A title=ORACLE-L@fatcity.com 
  href="mailto:ORACLE-L@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