| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: How to define a Type with multiple columns ,for bulk fetch
Juan,
What version of oracle are you on?
I believe bulk fetch into a composite array is 9i and higher.
this works on 9i:
declare
type array is table of emp%rowtype index by binary_integer;
recs array;
begin
select *
bulk collect into recs
from emp;
dbms_output.put_line( recs.count() );
recs.delete();
declaring the array %rowtype is safe because a select * will be in the same column sequence as the array structure. The columns in the select have to be in the same order as in the array. So, if instead of using %rowtype, make sure the record you define has the same structure as your underlying table ( or the sequence of columns in the select ).
if you still have problems, use a record of arrays instead of array of records:
declare
type ename_array is table of emp.ename%type index by binary_integer;
type sal_array is table of emp.sal%type index by binary_integer;
type emp_rec is record ( enames ename_array, sals sal_array );
recs emp_rec;
select ename,sal
bulk collect into recs.enames,recs.sals
from emp;
hope that helps,
-----Original Message-----
From: Juan Cachito Reyes Pacheco [mailto:jreyes_at_dazasoftware.com] Sent: Wed 3/10/2004 4:42 PM To: oracle-l_at_freelists.org Cc: Subject: How to define a Type with multiple columns ,for bulk fetchHi sorry another question more, how can I define a type with TWO OR MORE columns
DECLARE
TYPE TEST IS TABLE OF VARCHAR2(20);
TEST1 test;
i number := 0;
cursor c is SELECT LQF_CUENTA FROM CTALIQFON;
BEGIN
open c;
loop
fetch c bulk collect into test1;
for i in 1..test1.count loop
dbms_output.put_line(test1(i));
end loop;
exit when c%notfound;
end loop;
For one column I can do this
TYPE TEST IS TABLE OF VARCHAR2(20);
If I use a table I can do this
TABLA TYPE TEST IS TABLE LATABLA%ROWTYPE;
Now if I do something like
TYPE tLiq IS RECORD (
CTS_CUENTA VARCHAR2(20),
CTS_MONEDA VARCHAR2(3));
fetch c bulk collect into tLiq, gives error. pls-00403
Thanks
 |
 |