Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2002

    Unanswered: Distributed transaction error

    I am getting the following error when I execute rollback statement in PL/SQL stored procedure which is getting called through a tool
    connected by ODBC driver for oracle on win nt.

    [Oracle][ODBC][Ora]ORA-02074: cannot ROLLBACK in a distributed transaction
    Can any one tell me what is creating this error?

  2. #2
    Join Date
    May 2002


    I am having the same problems... at least I tested it with procedures that take recordsets as arguments... do you get it also at simple stprcs?

    please tell me if you hva found any solution to it.

    I tryed to avoid this error including DistribTx=0 in the connection string but it didnt help.

    Thanks in advance!

  3. #3
    Join Date
    Aug 2002
    I have a work around if you are using VB and ADO. If you are using another language they should provide the same functionality...with different syntax.
    Oracle is not going to let you perform a COMMIT or ROLLBACK in a stored procedure.
    Remember that in VB by default every database access (update, Insert, Stored Procedure call...) is committed
    You can override this by using . BeginTrans, .CommitTrans and .RollbackTrans.

    Here is my code that calls an Oracle Stored Procedure (function) and will commit or rollback the transaction depending upon the error returned from the function.

    Dim cnn As New ADODB.Connection
    Dim cmd As New ADODB.Command
    Dim strMine As String
    Dim CurrentMonth as Date
    Dim strProduct as String
    Dim ReturnValue as String

    strProduct= "Jack"
    CurrentMonth = #01/01/02#

    cnn.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=MyData;User Id=MyID;Password=pass;"
    cnn.BeginTrans '---- Begin a NEW transaction for this session!!
    strMine = String(500, " ")
    With cmd
    .ActiveConnection = cnn
    .CommandText = "myStoredFunction"
    .CommandType = adCmdStoredProc
    .Parameters.Append .CreateParameter(, adVarChar, adParamReturnValue, Len(strMine), strMine)
    .Parameters.Append .CreateParameter(, adVarChar, adParamInput, Len(strProduct), strProduct)
    .Parameters.Append .CreateParameter(, adDate, adParamInput, , CurrentMonth)
    ReturnValue= .Parameters(0)
    End With ' cmd
    Set cmd = Nothing
    ReturnValue= Trim(ExplodeBOM)
    If ReturnValue= "OK" Then
    cnn.CommitTrans '--- Commit the Transaction..
    cnn.RollbackTrans '--- Rollback the Transaction…because something failed.
    End If
    Set cnn = Nothing

  4. #4
    Join Date
    Mar 2004
    the solution is to add ";distribtx=0" to your connectionstring.

    For example:
    con.ConnectionString = "Data Source=" + g_sWMSDatabase & _
    ";User ID=" + g_sWMSDBUser & _
    ";Password=" + g_sWMSDBPass & _

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts