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

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 youAnyway: do you really need to see that output? What will you do with it? End users certainly won't.

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.

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

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:
and also show what are reqired results of the query for example data.