Re: Explicit Cursor in a For Loop and crashing database

difficult to read your unformatted code so I didn`t go through it, but you have probably infinite loop somewhere. If so, then once any session run it, then it becames locked and you cannot recompile it. You have to kill the session.

Try to format your code (make it readable) and then you (or we) will easier identify the problems.

PrivateBenjamin_at_hushmail.com (April) wrote in message news:<54df0379.0309181348.3dd90147_at_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 Mon Sep 22 2003 - 05:27:31 CDT