Urgent: load data into TABLE TYPE table 1

[jluost1]

Check the following:
----------------------
TYPE task_rec_type IS RECORD
(id NUMBER, name VARCHAR2(25))

TYPE task_tbl_type IS TABLE OF task_rec_type
INDEX BY BINARY_INTEGER;
---------------------
I have a table (tasks) in database with two columns(id, name).
Now I want to select records from tasks and load into task_tbl_type.

WHAT IS THE BEST WAY?

Thanks.

 

[SantaMufasa]

JLuost1, Last time I checked, Oracle's PL/SQL does not support the accessing of a "TABLE of RECORDS", but strangely, it does support a "RECORD of TABLES". (Dima, SybaseGuru, Carp, et. al., could you confirm this for us?) So, with your indulgence, I have slightly restructured your design to accommodate this restriction. Here is my solution and output:

set serveroutput on
declare
TYPE number_stencil is table of number INDEX BY BINARY_INTEGER;
TYPE varchar2_stencil is table of varchar2(25) INDEX BY BINARY_INTEGER;
TYPE task_rec_type is record (id number_stencil, name varchar2_stencil);
r task_rec_type;
loc binary_integer;
begin
loc := 0;
for x in (select id, last_name from s_emp) loop
loc := loc + 1;
r.id(loc) := x.id;
r.name(loc) := x.last_name;
end loop;
for i in 1..loc loop
dbms_output.put_line (r.id(i)||': '||r.name(i));
end loop;
end;
/

1: Velasquez
2: Ngao
3: Nagayama
4: Quick-To-See
5: Ropeburn
6: Urguhart
7: Menchu
8: Biri
9: Catchpole
10: Havel
11: Magee
12: Giljum
13: Sedeghi
14: Nguyen
15: Dumas
16: Maduro
17: Smith
18: Nozaki
19: Patel
20: Newman
21: Markarian
22: Chang
23: Patel
24: Dancs
25: Schwartz

Let me know if this works for you.

Dave

 

[DeepDiverMom]

Cool ! I've always wondered how to do this. Now I know. Thanks.

 

[carp]

Dave -
A table of records:

SQL> declare
2 pe my_rec is record(x date, y number);
3 ype my_rec_table is table of my_rec index by binary_integer;
4 l_rec my_rec;
5 l_table my_rec_table;
6 begin
7 l_table(7).x := sysdate;
8 end;
9 /

PL/SQL procedure successfully completed.

 

[SantaMufasa]

Thanks, Carp, for the update. (That's why I love this forum...I learn something new just about every day!)

So, JLuost1, here is your revised code based on your original assertion:

declare
TYPE task_rec_type IS RECORD (id NUMBER, name VARCHAR2(25));
TYPE task_tbl_type IS TABLE OF task_rec_type INDEX BY BINARY_INTEGER;
r task_tbl_type;
loc binary_integer;
begin
loc := 0;
for x in (select id, last_name from s_emp) loop
loc := loc + 1;
r(loc).id := x.id;
r(loc).name := x.last_name;
end loop;
for i in 1..loc loop
dbms_output.put_line (r(i).id||': '||r(i).name);
end loop;
end;
/

1: Velasquez
2: Ngao
3: Nagayama
4: Quick-To-See
5: Ropeburn
6: Urguhart
7: Menchu
8: Biri
9: Catchpole
10: Havel
11: Magee
12: Giljum
13: Sedeghi
14: Nguyen
15: Dumas
16: Maduro
17: Smith
18: Nozaki
19: Patel
20: Newman
21: Markarian
22: Chang
23: Patel
24: Dancs
25: Schwartz

Cheers !

 

[sem]

I'd also suggest to look at BULK COLLECT clause, as in case of large nuber of records to process, it improves performance significantly. Its usage is limited slightly by only single-column (although object tables also) collections, but simple workaround exists. Thus, Dave's example may be rewritten as:

declare

TYPE tId IS TABLE OF s_emp.id%type;
TYPE tName IS TABLE OF s_emp.last_name%type;
mId tId;
mName tName;
begin

select id, last_name bulk collect into mId, mName from s_emp;

for i in 1..mId.count loop
dbms_output.put_line (mId(i)||': '||mName(i));
end loop;

end;

Regards, Dima

 

[sem]

I revised it and found that in 9i single-column table is no more a limit. The following code will work on 9i:


declare
TYPE task_rec_type IS RECORD (id NUMBER, name VARCHAR2(25));
TYPE task_tbl_type IS TABLE OF task_rec_type INDEX BY BINARY_INTEGER;
r task_tbl_type;

begin

select id, last_name bulk collect into r from s_emp;

for i in 1..r.count loop
dbms_output.put_line (r(i).id||': '||r(i).name);
end loop;
end;


Regards, Dima

 

[SantaMufasa]

Dima, You've got my vote !

 

[sem]

Dave, did you try it or just evaluated theoretical knowledge?

Regards, Dima

 

[SantaMufasa]

Dima, I tried it and liked it !

Dave

 

[sem]

I mean that it's deep night there, so I suppose you had no chance to try it on large amount of data

Regards, Dima

 

[sem]

Oh, I write so slowly :-(

Regards, Dima

 

[SantaMufasa]

You're right...it's 2:47 a.m....I just tried it on the Oracle s_emp (classroom) table. I don't need lots of data to be happy.

 

[jluost1]

That's wonderful! Thanks a million, folks.

I got a big package to write and my several tables have nearly 100 columns. The code will definitely help me to make it shorter and neater and faster.