Procedures dying after running to certain record
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;
/
