Re: PL/SQL Stored Procedure Cursor Tuning

hhuber_at_enlighten.com (Heidi Huber) wrote in message news:<c8d38a43.0202201039.7d295e4b_at_posting.google.com>...
> I've written a stored procedure (below) which will duplicate a large
> amount of linked plan data. It is run only once per year from within
> my Oracle development tool (Toad). When I run only a few plans (see
> first cursor, cDental), it works fine. When I run a large number of
> plans to duplicate, however, I get a rollback too old/segment too
> small error (ORA-01555).
>
> My question is if I'm using the cursors correctly. All the select
> cursors should only grab a snapshot of the data as it was when the
> stored procedure was first called (the cursor should not update based
> on inserted data). I don't want to change the rollback segment size
> since I'm working on a development copy of a client's database.
>
> Any suggestions would be much appreciated!
>
> Thanks in advance,
>
> Heidi Huber
> Internet Developer
>

Your problem is you are committing inside your loops. As you are inserting into the table you are selecting from this is one of the safest recipes to get ora-1555.
Commit after each individual end loop statement and you will be fine.

Regards

Sybrand Bakker
Senior Oracle DBA Received on Thu Feb 21 2002 - 09:19:33 CET