Re: Pro*C sqlca returns -1555

Bob Stewart wrote:
>
> I have a short Pro*C program to remove unwanted rows from a table. The
> program consists of a cursor, a main loop, a fetch, a delete, and a
> commit ever 100 rows (I've also tried 1, and 1000 rows). At some random
> seeming point during the execution of the program, I get a -1555 returned
> in sqlca from the fetch. I'm at a total loss on this. I have gotten
> around it, for now, by closing the cursor, reopening it, and continuing
> the loop when I encounter this, but this can't be right.
>
> The oracle support guy sent me an email that relates to rollback segments.
> I completely rebuilt the 120 MB of rollback segments to no avail. There
> is no-one else but me using the table. The table originally started out
> as an import of a customer's 8.8 million rows. I have used the same
> program with a different cursor to remove 3.3 million rows with no
> problems. I have other criteria that need to be applied to the table
> now to get it chopped down to what I need to send back to the customer.
>
> Any thoughts?
>
> TIA.
> --
> Bob Stewart ASE
> (310) 335-7152 (USA) Air Transport Systems
> bob_at_latcost1.alaao.ats.eds.com
>
> The opinions expressed above are my own, and may not
> represent those of my employer.

I have run into this in the past. I may be right/wrong, but I think Oracle is trying to do consistent block reads on your cursor, rebuilding with the data you have deleted even thought you don't need it. I got in this situation using a cursor fetch array loop once. The work around was to close and reopen the cursor after every commit. There are other work arounds but this is the easiest.

I went over this in detail with my instructor when I was attending Oracle7 Database Admin class a few years back. He confirmed the problem but I don't want to even try to quote him after all of these years. He did confirm that the work around that you and I describe is valid.

BOL (Best of Luck)

--
                       \\|//
                       (0-0)
           +-----oOO----(_)-----------+
           | Brian P. Mac Lean        |
           | Database Analyst         |
           | brian.maclean_at_teldta.com |
           | http://www.teldta.com    |
           +-------------------oOO----+
                      |__|__|
                       || ||
                      ooO Ooo