• 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
  • Ron McLeod
  • Jeanne Boyarsky
Sheriffs:
  • Paul Clapham
Saloon Keepers:
  • Tim Holloway
  • Roland Mueller
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
 
And when my army is complete, I will rule the world! But, for now, I'm going to be happy with this tiny ad:
Clean our rivers and oceans from home
https://www.kickstarter.com/projects/paulwheaton/willow-feeders
reply
    Bookmark Topic Watch Topic
  • New Topic