Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2011

    Error - Buffer overflow

    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;
    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
    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.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);
    --open c3;
    for c3rec in c3 loop
    --open c1(c3rec.contract_id);
    --fetch c1 into v_directoryagencyid;
    --close c1;
    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

    else v_directoryagencyid := NULL;
    end if;
    --dbms_output.put_line('dirid'||v_directoryagencyid) ;
    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;

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

  2. #2
    Join Date
    Nov 2004
    out on a limb
    my first suggestion would be for you to tell us what database you are using
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts