• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • paul wheaton
  • Paul Clapham
  • Ron McLeod
Sheriffs:
  • Jeanne Boyarsky
  • Liutauras Vilda
Saloon Keepers:
  • Tim Holloway
  • Carey Brown
  • Roland Mueller
  • Piet Souris
Bartenders:

access inner table in a loop

 
Greenhorn
Posts: 27
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi
I have a question on using Oracle 9i nest table here.
create or replace type fTable2 as TABLE of varchar2(100);
create table fNT2(
tID varchar(200) primary key,
tmpTable fTable2
) nested table tmpTable store as fnt2_ref;
insert into fNT2 values('myTableId', fTable2());
Another flat table is defined as flatTable(item,varchar(50)), I have (maybe) 20 thousand records in it and want to move them to the inner table of fNT2.
What I did was (inside a store procedure):
OPEN cur for select item from flatTable;
FETCH cur INTO v_Item1;
FETCH cur INTO v_Item2;
if cur%FOUND then
insert into THE(select tmpTable from fNT2 where tID = 'myTableId') values(v_Item1 || v_Item2);
end if;
It looks very simple, but the thing is inside the fetch loop, in order to access the inner table, I have to do this "select tmpTable from fNT2 where tID = 'myTableId'"
again and again.
Does somebody have a good idear on how to access the inner table inside the loop without repeating that selection?
Thanks a lot.
Howard
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic