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

    Unanswered: PLSQL Error : Buffer overflow

    Hi Friends,

    Below is my anonymous nlock which is working fine when cursor c3 fetch less than 100 records and getting the buffer overflow error when fetching more than 100 records. please help with your suggesstion and solution. I tried with set setver output on size 100000; but still have the same problem.


    As i am using the prod i am not able to create the temp table

    here is my code..
    set serveroutput on;
    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;

    Also help to tune this code..

    Thanks ,
    Kohila

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    All DBMS_OUTPUT.PUT_LINE statements are commented so ... are you sure that this piece of code returns that error? Besides, buffer size of 100.000 is puny. Why don't you
    Code:
    SQL> set serveroutput on size unlimited;
    Anyway: do you really need to see that output? What will you do with it? End users certainly won't.

  3. #3
    Join Date
    Jan 2012
    Posts
    84
    Quote Originally Posted by Kohilavani View Post
    Also help to tune this code..
    Don't use PL/SQL. Use straight, single SQL query.
    Your code actually performs a simple join of three table, but in the most ineffective way,
    fetching row by row from the first cursor c1, then for each row from c1 opening, fetching (row by row) and closing the second cursor c2, and then the same with third cursor c3.
    Row by row = slow by slow.
    Remember that fetching each row from the cursor in PL/SQL code requires context switch between sql and pl/sql engines, and that is very costly ( open google and search for "pl/sql context swich").

    Combine your 3 cursors into one SQL query, this is the most effective way.
    Last edited by kordirko; 01-31-12 at 18:09.

  4. #4
    Join Date
    Dec 2011
    Location
    India
    Posts
    3
    Hi kordirko,

    Thanks for your suggesstion. I am new to this sql ans plsql world. can you please help me to make this join query.

    My requirement:

    have to take the contract id which doesnt have the combination of contract group and directory agency by checking from the table contractgroup and cnrtcntrgroup. for mapping i should check for the address of contract and take the contract id which are matched and check for the contractgroup and directory agency mapping. (this is my cursor 3)...will i be able to make all the three query in single query?

    can you please let me know how....or give me any example code if you have..that will be more help full.

    thank you

    Regards,
    Kohila

  5. #5
    Join Date
    Jan 2012
    Posts
    84
    Hello Kohila,

    basically you must take queries form cursors c1, c2 and c3 and combine them
    with a join or using a subquery.
    If you want someone to assist in creating that query, please post us create table statements for your tables plus insert instructions with some sample data, for example like this:
    Code:
    create table contract(
      contract_id number,
      producer_code varchar2(100),
      master_contract_id number
    );
    insert into contract
    select 1, '008862', 1 from dual union 
    select 2, '008862', 1 from dual union
    select 3, '008863', 2 from dual ;
    
    create table cntrctcntrctgroup(
      contract_id number,
      contractgroup_id number
    );
    
    insert into cntrctcntrctgroup values( 1, 1 );
    insert into cntrctcntrctgroup values( 1, 2 );
    ....
    etc.
    and also show what are reqired results of the query for example data.

Posting Permissions

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