Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2011
    Location
    India
    Posts
    3

    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;
    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.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    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
  •