RE: How to define a Type with multiple columns ,for bulk fetch
- From: "Anthony Molinaro" <amolinaro@xxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 10 Mar 2004 17:27:21 -0500
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();
end;
/
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;
begin
select ename,sal
bulk collect into recs.enames,recs.sals
from emp;
dbms_output.put_line( recs.enames.count() );
recs := null;
end;
/
hope that helps,
- ant
-----Original Message-----
From: Juan Cachito Reyes Pacheco [mailto:jreyes@xxxxxxxxxxxxxxxx]
Sent: Wed 3/10/2004 4:42 PM
To: oracle-l@xxxxxxxxxxxxx
Cc:
Subject: How to define a Type with multiple columns ,for bulk fetch
Hi sorry another question more, how can I define a type with TWO OR MORE
columns
to bulk fetch in this way
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
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
