Re: Cursor: does the job but doesn't come back

marknal_at_perth.DIALix.oz.au (Mark Brayshaw) wrote:

>I'm having an annoying problem with a cursor in SQL*plus.
>I have a table of about 42000 rows that I have just loaded using SQLLoader.
>One of the columns is a not_null containing the soundex of another column
>of the same table. For load purposes I fill this column with a constant,
>then I run a routine to loop through the table and update the column
>with the correct soundex'd value.
> I run the routine as anonymous block out of SQL*PLUS. I define the cursor
>as a select for update where the column contains the constant. I loop for
>500 records

Select for update, so I assume there's no "commit" embedded in your loop, correct? And you're updating the same table that is used by your cursor, right? So your rollback segments are growing like mad while Oracle tries to maintain the read consistent model...

Does it *ever* come back (i.e. if you give it TONS of time?) Does the session remain ACTIVE the whole time? Have you tried not using "select for update" and then putting commits in your loop?

I can't honestly say that I know for sure that this is your problem, it just sounds familiar...

Mike Received on Sat Apr 13 1996 - 00:00:00 CEST