OracleException with respect to Connection issues or Database down

Question

Say I have following query

private void updateusers()
{
    List<int> listRecords=new List<int>();
    string strQuery="select * from table where role='Admin' and logged_in<=sysdate-1";
    OracleCommand com=new OracleCommand(strQuery,objConnection);
    OracleDataReader reader=com.ExecuteReader();
    if(reader.HasRows)
    {
         while(reader.read())
         {
              listRecords.Add(int.Parse(reader["Row_ID"].toString()));
         }
         int i=0;
         foreach(int row in listRecords)
         {
              try
              {
                    OracleCommand command=new OracleCommand();
                    command.Connection=objConnection;
                    command.CommandText="Update table set Status='Y' where Row_ID="+listRecords[i];
                    command.CommandType=System.Data.CommandType.Text;
                    command.ExecuteNonQuery();
              }
              catch(OracleException ex)
              {
                    //log the exception
              }
         }
    }
}

Now my problem is, Lets assume select query fetches 2000 records and foreach will continue to update each record and assume at 500th record either DB connection is lost or say Database is down for some reason. Now in these scenarios I want to iterate or try updating same record for 3 times and if it fails 3rd time, come out of foreach-loop and stop executing update commands for remaining 1500 records.

So is there any particular way to identify these type of Oracle Exceptions or better say Environmental exceptions? Does OracleException provide any particular messageCode for these type of exceptions?

You didn't ask, but even assuming this is a trivial example (it seems a single query would accomplish the update in your example), cases like these seem more suited towards bulk inserts/updates. It doesn't address your issue, but it may actually resolve the deeper issue by executing all updates in a single transaction, or at the most a few transactions (say one transaction per 5,000 or so updates) — Hambone, Dec 23 '15 at 15:45
@Hambone.. You question is valid but I am having log for each records and this I cannot do with single `update` so I have to update individually.. — Guruprasad J Rao, Dec 23 '15 at 17:05

score 1 · Accepted Answer · answered Dec 23 '15 at 05:29

1

You can use ErrorCode property of OracleException class to identify particular type of error.

answered Dec 23 '15 at 05:29

Akshey Bhat

8,227
1
20
20

Yea but I wanted to know what is the `ErrorCode` for those scenarios. – Guruprasad J Rao Dec 23 '15 at 06:15
1

You need to reproduce the error you want to detect and inspect ex.ErrorCode for it's value – Akshey Bhat Dec 23 '15 at 06:20

OracleException with respect to Connection issues or Database down

1 Answers1