Explicit Cursor in a For Loop and crashing database

From: April <PrivateBenjamin_at_hushmail.com>
Date: 18 Sep 2003 14:48:35 -0700
Message-ID: <54df0379.0309181348.3dd90147@posting.google.com>

I have this procedure below which keeps hanging and will evetually crash the database.... Oracle 7.3.4. The procedure itself seems to be corrupted and cannot be compiled or dropped after trying to run it or step through debugging with Toad.

Is there something wrong with the way I am using the explicit cursors in a For Loop?

I have stepped through the procedure (when it doesn't crash) for small data sets ie. one item for the main cursor loop (c_chld), and it works fine, but when it has to go through another main loop then it crashes, even though it is doing the exact same thing.

Thanks for your help,
April

CREATE OR REPLACE procedure align_xrsl_xrsip is /*PURPOSE: Align xref_structure_insulator_poas (xrsip) with xref_structure_linesection (xrsl)
METHOD: for each record that was added to xrsl, get the original from xrsip and insert a copyinto xrsip with the new structure_cct_id and i_s, o_s.
update the original xrsip o_s to min i_s of related recs added to xrsl*/

• original parent (xrsl.lnsection_id) with new children
  (xrsl.lnsection_id)
  cursor c_chld is select * from TEMP_PARENT_CHILD order by parnt,chld;

--source dataset for insertion of new insulator recs cursor c_parnt (scctid number) is select * from xref_structure_insulator_poas where structure_cct_id = scctid;

--old structure_cct_id with the new structure_cct_id cursor c_xrslids (p_id number, c_id number) is select a.oldid old_scctid, b.newid from
(select lnsection_id parnt,id oldid,structure_id from
xref_structure_linesection where lnsection_id = p_id)a,
(select lnsection_id chld,id newid,structure_id from
xref_structure_linesection where lnsection_id = c_id)b, temp_parent_child c
where

a.parnt = c.parnt and
b.chld = c.chld and
a.structure_id = b.structure_id

order by a.oldid,b.newid;

new_os date;
exist number;

begin

• for the new xrsl records for chld in c_chld loop --get the new and old structure_cct_ids for cid in c_xrslids (chld.parnt,chld.chld) loop
  • get the source xrsip records for copying exist := 0; For src in c_parnt(cid.old_scctid) loop exist := 1;
    • insert the copy of xrsip with updated columns for new structure_cct_id insert into xref_structure_insulator_poas (INSULATOR_POAS_ID, STRUCTURE_CCT_ID, INSULATOR_CODE_ID, CONDUCTOR_POSITION_ID, ORIENTATION, NO_UNITS, NO_STRINGS, INSULATOR_ID, I_S, O_S, UPDATED, UPDATED_BY, DISTCONDATTACHABOVEGNDFT, ATTACHED_TO_ID, CONDUCTOR_DAMPER_TYPE_ID, CONDUCTOR_DAMPER_QTY) values (src.INSULATOR_POAS_ID, cid.newid, src.INSULATOR_CODE_ID, src.CONDUCTOR_POSITION_ID, src.ORIENTATION, src.NO_UNITS, src.NO_STRINGS, src.INSULATOR_ID, chld.i_s, chld.O_S, sysdate, '180490', src.DISTCONDATTACHABOVEGNDFT, src.ATTACHED_TO_ID, src.CONDUCTOR_DAMPER_TYPE_ID, src.CONDUCTOR_DAMPER_QTY); end loop;
      • get the new o_s for original xrsip rec if exist = 1 then select min_is into new_os from TEMP_NEW_OS_XRSI_POAS where parnt = chld.parnt and id = cid.old_scctid;
    • close the original records - update the o_s to min is of new records update xref_structure_insulator_poas set o_s = new_os where structure_cct_id = cid.old_scctid; end if; end loop; end loop;

exception

     when others then
       rollback;
	   raise;

end;
/ Received on Thu Sep 18 2003 - 16:48:35 CDT