• Post Reply Bookmark Topic Watch Topic
  • New Topic

access inner table in a loop  RSS feed

 
Howard Zhao
Greenhorn
Posts: 27
  • Mark post as helpful
  • send pies
  • 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
 
It is sorta covered in the JavaRanch Style Guide.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!