Re: Pro*C sqlca returns -1555

In <3260964C.59ED_at_teldta.com> "Brian P. Mac Lean" <brian.maclean_at_teldta.com> writes:

>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.
>>
 

>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.

Instead of closing and reopening after every commit (which sounds like a real drag on performance), why not just code the program to trap the -1555 error and close/reopen/restart processing loop only then? I code a "WHILE NOT done" loop, with the cursor loop contained by a BEGIN block whose EXCEPTION traps the -1555. If the cursor loop finishes normally, the "done" flag is set true. Otherwise, the LOOP causes the inner cursor loop to be repeated.

-- 
------------------------------------------------------------------------------
Computer General		Rochester, NY			(716) 436-6372
      "... providing general computer solutions to specific business problems"
                              gwn_at_servtech.com