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;
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);
Thanks so much for your response. What I mean by dying was when I do a count on Dave_temp2, it insert records into that table and after certain record it hang and not doing anything. I don't get any ORA- at all.
seem like the resource issues b/c on the following cursor:
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;
if I comment out more condition, more records will be inserted and if I take the comment out on the order by less record will be inserted
if the procedure is hanging, it may mean that there are some locking issues going on with the procedure. hanging usually indicate resource contention and the fact that commenting out more code seems to work for a little while suggests this too. Check your locking/waiting views for that procedure. Good luck.
if it was hanging you would see latch free, enqueue which are blocking mechanisms otherwise itīs doing work but if your cursor is slow then you see what you see
Reply With Quote
Bookmarks