IntelliJ Java IDE
The moose likes Oracle/OAS and the fly likes access inner table in a loop Big Moose Saloon
  Search | Java FAQ | Recent Topics
Register / Login
JavaRanch » Java Forums » Products » Oracle/OAS
Reply Bookmark "access inner table in a loop" Watch "access inner table in a loop" New topic
Author

access inner table in a loop

Howard Zhao
Greenhorn

Joined: May 06, 2002
Posts: 27
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
 
IntelliJ Java IDE
 
subject: access inner table in a loop
 
Threads others viewed
sql server callable statement result set issue..
DELETE using subquery
What is the errorin this procedure??
calling oracle procedures in java
not all variables bound Error
developer file tools