Home » SQL & PL/SQL » SQL & PL/SQL » bulk collect
bulk collect [message #268599] Wed, 19 September 2007 02:43 Go to next message
ora_enthu
Messages: 2
Registered: September 2007
Location: Mumbai
Junior Member
in the below code i am pondering what to put in 'condition'... i want the loop to be exited whenever the records are exhausted. will this happen automatically or do i have to write that in the condition. Also, is there any chance of exception?


declare

type Rec1 is record (
CustAct cust_account.cust_ac_no%type,
LocBrn customer.local_branch%type,
Ver customer.Version_No%type,
CustTyp customer.c_Type%type);

TYPE RC
IS
TABLE OF
Rec1
INDEX BY BINARY_INTEGER;


cursor c is
select b.cust_ac_no, a.local_branch, a.version_no, a.customer_type from sttms_customer a, sttms_cust_account b
where a.customer_no=b.cust_no and a.auth_stat='A' and a.record_stat='O' ;
val RC;
record_key varchar2(50);
lim number :=500;
begin
open c
while 'Condition' loop
fetch c bulk collect into val limit lim;
For i in val.first..val.last loop
record_key := i.local_branch||'~'||i.cust_ac_no||'~'||i.version_no||'~';
if i.c_type='I' then
insert into cstws_udf_vals(id,key,field,val) values('A',record_key,'B','N');
else
insert into cstws_udf_vals(id,key,field,val) values('A',record_key,'B','Y');
end if;
end loop;
end loop;
close c;
end;
Re: bulk collect [message #268601 is a reply to message #268599] Wed, 19 September 2007 02:49 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Take a look at the example here.

You can check with C%ROWCOUNT = 0 after the fetch.

Ross Leishman
Previous Topic: Putting Identification flag with query
Next Topic: my procedure generating different files bt want it to generate single file
Goto Forum:
  


Current Time: Sun Dec 04 00:21:36 CST 2016

Total time taken to generate the page: 0.09615 seconds