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

    Unanswered: Distributed transaction error

    Hi,
    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?
    thanks,
    paul

  2. #2
    Join Date
    May 2002
    Posts
    1

    ORA-02074

    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
    Location
    Colorado
    Posts
    1
    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.Open
    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)
    .Execute
    ReturnValue= .Parameters(0)
    End With ' cmd
    Set cmd = Nothing
    ReturnValue= Trim(ExplodeBOM)
    If ReturnValue= "OK" Then
    cnn.CommitTrans '--- Commit the Transaction..
    Else
    cnn.RollbackTrans '--- Rollback the Transaction…because something failed.
    End If
    cnn.Close
    Set cnn = Nothing

  4. #4
    Join Date
    Mar 2004
    Posts
    1
    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 & _
    ";distribtx=0"

Posting Permissions

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