Please help prevent data layer refactoring of this ODP.NET code and transactions

Question

I am using Oracle 11g client, with ODP.NET. I am trying to add conditional Transaction handling.

Dim ds As New DataSet()
Dim txn As OracleTransaction
Dim _beginTransaction as Bolean = true
 Using conn As New OracleConnection(ConnString)
            Try
                conn.Open()
                If _beginTransaction Then
                        txn = conn.BeginTransaction(IsolationLevel.Serializable)
                End If

                Dim adapter As OracleDataAdapter = New OracleDataAdapter()
                adapter.SelectCommand = New OracleCommand(sSQL, conn)

                For i As Integer = 0 To UBound(parameters, 1)
                    adapter.SelectCommand.Parameters.Add(parameters(i))
                Next

                adapter.Fill(ds)
                If _beginTransaction Then
                    txn.Commit() //txn is undefined here? why?
                End If


            Catch e As Exception
                txn.Rollback()
            End Try
        End Using

How do I fix txn being nothing / null? The error is: Variable 'txn' is used before it has been assigned a value. A null reference exception could result at runtime. Links or pointers to solutions would be appreciated also.

Edit: Thanks to RichardOD for pointing out that you can not explicitly declare that a transaction cannot be opend up on stored procedures via ODP.NET. I have verified that this is an issue. BUT We still haven't figured out why the error is occuring. I understand that txn is initially given a value w/in an if statement, but being defined outside of the try/catch block should make that irrelevant.... right? Or is that bad coding?

Answer 1

Assuming _beginTransaction is a boolean have you set it to true before If _beginTransaction Then ?

Also have you committed the previous transaction before starting this one? Oracle can do weird stuff with connection pooling and BeingTransaction.

A long time ago I had a bug similar to this. Have you looked here?

Edit- are you trying to call a .NET stored proc? OracleConnection.BeginTransaction does not support stored procedure calls:

OracleConnection.BeginTransaction is not allowed for .NET stored procedure

Answer 2

Question: Is it null immediately after being asigned? And if not, when does it become null? If it's null after immediately, it might be the connection pooling stuff. If after getting the adapter from conn or after filling it, then it's even crazier...

But I would try and find out

Answer 3

Oracle does not require a transaction for selecting data. Why do you try open one?

EDIT:
If your vb code is called from oracle (via .net integration) than there is no transaction support as RichardOD wrote. Please clarify the environment.

The sql statement executed is dynamic and given in sSQL. The command is prepared and given to a DataAdapter that fills a DataSet. Than you can only execute SELECT statements. Otherwise there is no result.

OR

Because the parameters are prepared too. You are calling a stored procedure (without telling the the CommandType is StoredProcedure). One of your parameters is a ref cursor parameter which will fetched into the DataSet. Right?

Oracle does not need explicit transactions as sql server does. Oracle starts an implicit transaction with the first dml statement in your session. The sideeffect is, if you did not start an transaction you cannot commit the implicit transaction. I do not know if there is access to implicit transaction via the connection object.