Home » SQL & PL/SQL » SQL & PL/SQL » how to insert data from ref table(collection) into a table in a loop
how to insert data from ref table(collection) into a table in a loop [message #36162] Thu, 08 November 2001 06:02 Go to next message
Srinivas
Messages: 138
Registered: June 2000
Senior Member
Hi,

I have created a type record as

type erec is record(
count tab.matchcount%type);

type tabty as table of erec index by binary_integer;

datac:= tabty;
after processing ,i get some output like īthis in the type datac
[[102]] 0
[[1200]] 2 ....

Now,how do i loop and transfer the data from datac into a table having 2 columns(col1,col2).

Any help is appreciated.

Srinivas

----------------------------------------------------------------------
Re: how to insert data from ref table(collection) into a table in a loop [message #36172 is a reply to message #36162] Thu, 08 November 2001 08:45 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
does this help??

create table cat_copy as (select * from cat where rownum < 1);

DECLARE
TYPE cat_pltab_type IS TABLE OF cat%ROWTYPE INDEX BY BINARY_INTEGER;
cat_pltab cat_pltab_type;
CURSOR c1 IS SELECT table_name, table_type FROM cat;
i NUMBER;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO cat_pltab (cat_pltab.COUNT + 1);
EXIT WHEN c1%NOTFOUND;
END LOOP;
FOR i IN 1 .. cat_pltab.COUNT
LOOP
insert into cat_copy(table_name, table_type) values (cat_pltab (i).table_name, cat_pltab (i).table_type);
END LOOP;
END;
/

----------------------------------------------------------------------
Re: how to insert data from ref table(collection) into a table in a loop [message #36191 is a reply to message #36172] Fri, 09 November 2001 00:46 Go to previous messageGo to next message
Srinivas
Messages: 138
Registered: June 2000
Senior Member
I have created a type record as

type erec is record(
count tab.matchcount%type);

type tabty as table of erec index by binary_integer;

datac:= tabty;
after processing ,i get some output like īthis in the type datac
[[102]] 0
[[1200]] 2 ....

Now,how do i loop and transfer the data from datac into a table having 2 columns(col1,col2).

I want to transfer the data of index into one column(like values 102,1200) and the other actual values tab.matchcount into another column of table.

I am confused as how to read the value of the index and the other column from the ref table .

Any help is appreciated.

----------------------------------------------------------------------
Re: how to insert data from ref table(collection) into a table in a loop [message #36210 is a reply to message #36172] Fri, 09 November 2001 13:11 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
I don't really understand the problem. When you say INDEX, do you mean an Oracle Table Index - or the index of the pl/sql table?? if you just want to move data from one table to another this just SQL is required.
insert into TAB2 (col1, col2) select col1, col2 from TAB1;

Why do you need the pl/sql table (... index by binaty integer)??

begin
for rec in (select col1, col1, col3 from T) loop
insert into T2 (col1, col2) values(rec.col1, rec.col2);
insert into T3 (col3) values(rec.col3);
end loop;
end;

----------------------------------------------------------------------
Previous Topic: how to create cursor with bind variables
Next Topic: CREATE TRIGGER
Goto Forum:
  


Current Time: Thu Mar 28 04:04:00 CDT 2024