Hi friends,

I have tried to exceute this anonymous block in my production enviornment. Its working for 70 records - 100 records. but getting buffer overflow error for more than 100 records...

Please help here with your suggesstion and solution. I used 3 cursor within loop.

here is my code..

set serveroutput on size 100000;
declare
v_directoryagencyid number;
v_contractgroup_id number;
cursor c3 is
select c.contract_id --,c.producercode,c.master_contract_id,ccg.contractg roup_id ,g.DIRECTORYAGENCYID
from contract c,cntrctcntrctgroup ccg,contractgroup g
where
c.contract_id = ccg.contract_id(+)
and ccg.contractgroup_id = g.contractgroup_id(+)
and ccg.contractgroup_id is null
and regexp_like(c.producercode,'^(008861)','i'); --00882363;regexp_like(c.producercode,'^(008816)','i ');c.producercode = 00881621;
cursor c1(lv1_contractid number) is
select contract_id from ebcag.address a where regexp_replace(addressline||city||state||zip,'[^A-Za-z0-9]')
in(select regexp_replace(b.addressline||b.city||b.state||b.z ip,'[^A-Za-z0-9]') from ebcag.address b where b.contract_id = lv1_contractid and addresspurpose = 'LOCATION') and addresspurpose = 'LOCATION';
cursor c2(lv_contractid number)is
select g.directoryagencyid
from contract c,cntrctcntrctgroup ccg,contractgroup g
where c.contract_id = lv_contractid
and c.contract_id = ccg.contract_id
and ccg.contractgroup_id = g.contractgroup_id;--i_contractid);
begin
--open c3;
for c3rec in c3 loop
--open c1(c3rec.contract_id);
--fetch c1 into v_directoryagencyid;
--close c1;
--dbms_output.put_line(c3rec.contract_id);
for c1rec in c1(c3rec.contract_id) loop
open c2(c1rec.contract_id);
--dbms_output.put_line('contract_id'||c1rec.contract _id);
fetch c2 into v_directoryagencyid;
close c2;
if v_directoryagencyid is not null THEN

--dbms_output.put_line(v_contractgroup_id);
EXIT;
else v_directoryagencyid := NULL;
end if;
--dbms_output.put_line('dirid'||v_directoryagencyid) ;
--dbms_output.put_line(c3rec.contract_id);
end loop;
select contractgroup_id into v_contractgroup_id from contractgroup where directoryagencyid = v_directoryagencyid;
--dbms_output.put_line(c3rec.contract_id||' '||v_directoryagencyid||' '||v_directoryagencyid);
end loop;
--close c3;
end;

As this is the production env i doesnt have priviledge to create a temp table.

my first suggestion would be for you to tell us what database you are using