I have this procs and it's dying after running to certain number. Does anyone have any advises???

Thanks



CREATE OR REPLACE PROCEDURE SPATIAL.DUPLICATE_MULTI_CNTY_TWPS(p_st_code_list IN varchar2) is

cursor all_states is
select grid_lvl2_code
from grid_master
where grid_lvl = 2
--and grid_generatable = 'Y'
and grid_priority = 1;

cursor certain_states(c_st_code_list IN varchar2) is
select grid_lvl2_code
from grid_master
where grid_lvl = 2
and grid_priority = 1
--and grid_generatable = 'Y'
and grid_lvl2_code in (c_st_code_list);

cursor all_counties_by_state(c_st_code_cnty IN char) is
select grid_key
--, grid_lvl3_code, grid_lvl3_name, grid_fips_cnty_code
from grid_master
where grid_lvl = 3
and grid_priority = 1
--and grid_generatable = 'Y'
and grid_lvl2_code in (c_st_code_cnty);
--testing
--and grid_lvl3_name = 'Campbell'
--testing
--order by grid_lvl3_name;


cursor all_twps_near_cnty(c_cnty_grid_key IN varchar2, c_grid_lvl3_code IN spatial.grid_master.grid_lvl3_code%type) is
select a.grid_key
--, a.grid_lvl3_name
from grid_master a, grid_master b
where a.grid_lvl = 4
--and grid_generatable = 'Y'
and a.grid_priority = 1
and a.grid_lvl3_code <> c_grid_lvl3_code
--and grid_lvl2_code in (c_st_code_twp)
and b.grid_key = c_cnty_grid_key
and sdo_filter(a.sob_geometry, b.sob_geometry, 'querytype = WINDOW') = 'TRUE';
--order by a.grid_lvl3_name;

twp_count integer;
new_twp_key spatial.grid_master.grid_key%type;
mask varchar2(40);
v_twp_geometry mdsys.sdo_geometry;
v_cnty_geometry mdsys.sdo_geometry;
v_grid_lvl3_code spatial.grid_master.grid_lvl3_code%type;

begin

for each_state in certain_states(p_st_code_list) loop

for each_cnty in all_counties_by_state(each_state.grid_lvl2_code) loop

/*select sob_geometry
into v_cnty_geometry
from grid_master
where grid_key = each_cnty.grid_key;
*/

select grid_lvl3_code
into v_grid_lvl3_code
from grid_master
where grid_key = each_cnty.grid_key;

for each_twp in all_twps_near_cnty(each_cnty.grid_key,v_grid_lvl3_code) loop
/*
select sob_geometry
into v_twp_geometry
from grid_master
where grid_key = each_twp.grid_key;
*/
insert into dave_temp2(A,B)
values(each_cnty.grid_key,each_twp.grid_key);

SELECT SDO_GEOM.RELATE(a.sob_geometry, m.diminfo, 'DETERMINE', b.sob_geometry, m.diminfo)
into mask
FROM user_sdo_geom_metadata m, grid_master a, grid_master b
WHERE m.table_name = 'GRID_MASTER'
and m.column_name = 'SOB_GEOMETRY'
and a.grid_key = each_twp.grid_key
and b.grid_key = each_cnty.grid_key;

IF mask = 'OVERLAPBDYINTERSECT' THEN

/*
select count(grid_key)
into twp_count
from grid_master
where grid_lvl = 4
and grid_lvl2_code = each_state.grid_lvl2_code
and grid_lvl3_code = each_cnty.grid_lvl3_code
and grid_priority = 1
and grid_generatable = 'Y';

if twp_count = 1 then
*/
insert into dave_temp(A,B)
values(each_cnty.grid_key,each_twp.grid_key);


--exit;

end if;

commit;

--end if;

end loop;

end loop;

end loop;

end;
/