Re: ORA-01555 on a long select after many small txs from the same connection ?

Hi Nick,

this is a standard problem when using a long cursor loop and updating the rows you retrieved - directly or indirect via trigger actions or from other transactions. Keep in mind, oracle allows you to fetch across commit points without invalidating the cursor - in contrast to the ANSI standard. Try to fill a temporary table with the outer loop PKs (primary keys) and commit. Then redesign the outer loop to select from this temp table. After finishing all you can drop the table. This should avoid the ORA-01555.

On Fri, 25 Jun 1999 13:24:40 +0100, "Nick Rumble" <rumbln_at_tetraworld.com> wrote:

>I have an OCI application that issues a select statement to retrieve every
>row from a transaction table.
>
>The application fetches a row from the result set and for each of these, it
>updates a corresponding row on another table (a kind of "extension" table).
>
>Each row is processed as a separate transaction.
>
>After processing 300,000 rows (and therefore 300,000 txs), we get the 01555
>error.
>
> :
>
>Can anyone explain what is happening here ?
>
>Many thanks
>
>Nick Rumble
>
>

__
Fred Stojentin Received on Fri Jun 25 1999 - 08:03:39 CDT