How to resume after an Oracle Error ORA-00054

[Jawahar]

All,
I have a C# application with an Oracle backend. There is a fair amount of
locking being used to prevent two users from update the same records. So we
use the Oracle FOR UPDATE NOWAIT in our SQL calls. This works fine and the
second user is not allowed to access the same record as the first user.

Currently we are using a try catch block to catch the Oracle exception
ORA-0054. Then we exit the subroutine.
I am trying to look for a way to see if I can resume the execution from the
failure point.

For example:
try
{
SQL call to DB using FOR UPDATE NOWAIT;
}

catch (System.Data.Or acleClient.Orac leException ex)
{

if (ex.Message.Ind exOf("ORA-0054")!=0)
do something;
}

When I get the exception I would like to try again up to three times before
finally quitting.
Is there a way of saying RESUME (an equivalent in C#) as in VB.net. I
realize I may need a loop of some kind or counter to keep track of the number
of attempts. I would like the code to try to execute the SQL again rather
than just exiting the first time we hit ORA-0054.

Any help or suggestions are always appreciated.

Jawahar

[Nicholas Paldino [.NET/C# MVP]]

Jawahar,

There is no ay to do this (with a resume, that is). You will have to
execute in a loop, like you said. Basically, have a counter that counts
down from the number of times you want to try, as well as a flag (bool) that
indicates whether or not the operation completed. Then, cycle through the
loop while the operation was not completed, and the counter is greater than
zero.

It should be pointed out that locking rows like this will kill
scalability, and this kind of "retry" semantics will become very unwieldy
and difficult to maintain.

Hope this helps.

--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard. caspershouse.co m

"Jawahar" <Ja*****@discus sions.microsoft .com> wrote in message
news:D6******** *************** ***********@mic rosoft.com...

All,
I have a C# application with an Oracle backend. There is a fair amount of
locking being used to prevent two users from update the same records. So
we
use the Oracle FOR UPDATE NOWAIT in our SQL calls. This works fine and the
second user is not allowed to access the same record as the first user.

Currently we are using a try catch block to catch the Oracle exception
ORA-0054. Then we exit the subroutine.
I am trying to look for a way to see if I can resume the execution from
the
failure point.

For example:
try
{
SQL call to DB using FOR UPDATE NOWAIT;
}

catch (System.Data.Or acleClient.Orac leException ex)
{

if (ex.Message.Ind exOf("ORA-0054")!=0)
do something;
}

When I get the exception I would like to try again up to three times
before
finally quitting.
Is there a way of saying RESUME (an equivalent in C#) as in VB.net. I
realize I may need a loop of some kind or counter to keep track of the
number
of attempts. I would like the code to try to execute the SQL again rather
than just exiting the first time we hit ORA-0054.

Any help or suggestions are always appreciated.

Jawahar