System.AccessViolationException - Oracle client tools

Question

Framework Version: v4.0.30319 
Description: The process was terminated due to an unhandled exception.
Exception Info: System.AccessViolationException
Stack:
  at Oracle.DataAccess.Client.OracleParameter.SetStatus(Int32)
  at Oracle.DataAccess.Client.OracleParameter.PreBind(Oracle.DataAccess.Client.OracleConnection, IntPtr, Int32, Boolean)
  at Oracle.DataAccess.Client.OracleCommand.ExecuteNonQuery()

My application is crashing with this exception. context is: We are trying to execute a stored procedure that accepts two parameters, we are using name binding for parameters in command text.

Example:

OraCommand.CommandText = "begin LOAD_UTILS.TRUNCATE_TABLE(:a1,:a2); end;"
OraCommand.Parameters.Add(New OracleParameter("a1", OracleDbType.Varchar2, 1000))
OraCommand.Parameters(0).Direction = ParameterDirection.Input
OraCommand.Parameters(0).Value = params(2)
OraCommand.Parameters.Add(New OracleParameter("a2", OracleDbType.Varchar2, 1000))
OraCommand.Parameters(1).Direction = ParameterDirection.Input
OraCommand.Parameters(1).Value = params(3)

The oddity i have observed is, we run these statements in a for loop for a certan number of retries incase if there are any errors, OraCommand is an instance variable, so parameters collection is not getting cleared.

In iteration#1: paratmeters a1, a2 are getting added
In iteration#2: the first two parameters are already there, and again we are adding parameters with names a1, and a2. ...

Am not seeing the issue, when i clear the parameter collection at the start of every iteration, but i am not able to come up with a theory that is causing the issue here, any thoughts?

Answer 1

@Mrinal Kamboj You are right, code is in VB.NET; The exception was thrown when it tries to execute ExecuteNonQuery. I tried WinDbg, and this what it gives:

Answer 2

You are using ODP.Net and way it works is, .Net C# APIs are just wrapper to expose the their service layer in C, which communicates with OCI (Oracle Call interfaces), mostly the access violation exception come from their service layer or OCI, because it is not really feasible to have Access Violation (AV) in the managed code (.Net C#), CLR doesn't allow it. Easier way to debug the issue would be to use Windbg, load the symbols and it will point you to exact method which is causing an issue, which will be the unmanaged code, but the challenge is you will have the release version with at most public symbols, which doesn't help much, other option would be to post the issue with code to the Oracle Technet, where the ODP.Net developer can provide a workaround and fix. However before that in my view there are certain issues with your code:

In a loop following lines will add a new parameter a1 and a2 everytime

OraCommand.Parameters.Add(New OracleParameter("a1", OracleDbType.Varchar2, 1000))
OraCommand.Parameters.Add(New OracleParameter("a2", OracleDbType.Varchar2, 1000))

The behavior that you are experiencing in expected, since only after creation of new parameters you are setting the value and direction at the index 0 and 1, which is same every time, in every iteration you end up adding two new parameters to the OracleCommand object and somewhere that is messing up with the internal structure, since what you are doing is incorrect. Correct way would be following code in the loop:

OracleParameter A1 = OracleParameter("a1", OracleDbType.Varchar2, 1000)
A1.Direction = ParameterDirection.Input
A1.Value = params[2]
OracleCommand.Parameters[0] = A1;

OracleParameter A2 = OracleParameter("a2", OracleDbType.Varchar2, 1000)
A1.Direction = ParameterDirection.Input
A1.Value = params[3]
OracleCommand.Parameters[1] = A2;

In fact another odd thing in your code is access to the collection like array with a following code:

OraCommand.Parameters(0).Value = params(2)

This is not possible in C#, you have to use square bracket [], In my view you must be using VB.Net, since even you new is not correct, it is not New in C#