Home » SQL & PL/SQL » SQL & PL/SQL » cannot ROLLBACK in a distributed transaction
| cannot ROLLBACK in a distributed transaction [message #37009] |
Thu, 10 January 2002 01:04  |
OngBC
Messages: 2
Registered: January 2002
|
Junior Member |
|
|
Hi
I have developed a stored procedure which will be call in VB6 using the ADO object and Oracle Provider for OLEDB.
In this stored procedure, it was accessing tables across two schema. And I have implemented Rollback when encounter error in this stored procedure.
Now the problem is when I called this stored procedure in VB it encountered error in the store procedure when trying to rollback, the error is "ORA-02074: cannot ROLLBACK in a distributed transaction".
Does it means I cannot perform a rollback across two schema?
I appreciate if somebody can enlighten me. Thanks
|
|
|
|
| Re: cannot ROLLBACK in a distributed transaction [message #37017 is a reply to message #37009] |
Thu, 10 January 2002 04:49  |
pratap kumar tripathy
Messages: 660
Registered: January 2002
|
Senior Member |
|
|
Hi,
1.is this procedure works fine in SQL+ ?
2.what is your oracle version ?if it is 7 then "A procedure called remotely cannot execute a COMMIT, ROLLBACK, or SAVEPOINT statement."
3.have u included "set distribtx=0" in the connection string in VB-code.
4.please give version information of softwares u r using, if u find problem again.
cheers
pratap
|
|
|
|
|
|
|
|
| Re: cannot ROLLBACK in a distributed transaction [message #38811 is a reply to message #37009] |
Thu, 16 May 2002 08:41 |
Dario Liberman
Messages: 1
Registered: May 2002
|
Junior Member |
|
|
Hello, I am having the ORA-02074 error: "cannot ROLLBACK in a distributed transaction" while trying to issue a rollback statement in a storedprocedure within a package.
The oracle version is 8.1.6
I am using the OleDBProvider that comes with this version.
it seems like if I am distributing the transaction, but I think I dont... any suggestions?
My VB function is this:
Public Function SPR_PATIENTS_UPDATE(ByRef o_ERRCODE As Long, ByVal i_ID As Long, ByVal i_FIRSTNAME As String, ByVal i_LASTNAME As String, ByVal i_DOCTYPE As String, ByVal i_DOCNUM As String, ByVal i_EMAIL As String, ByVal i_BIRDTHDATE As Date, ByVal i_SEX As String, ByVal i_COUNTRY As Long, ByVal i_CITY As String, ByVal i_ADDRESS As String, ByVal i_ZIPCODE As String, ByVal i_PHONENUMBER As String, ByVal i_DESCRIPTION As String, ByVal i_STATE As Long, Optional ByRef Conn As ADODB.Connection = Nothing) As ADODB.Recordset
Dim cmd As New ADODB.Command
If Conn Is Nothing Then
Set Conn = New ADODB.Connection
GetNewConnection.Open "Provider=OraOLEDB.Oracle;User ID=INEBA;Password=INEBA;Data Source=LEO2;FetchSize=200;CacheType=Memory;PLSQLRSet=1;DistribTx=0"
end if
cmd.ActiveConnection = Conn
cmd.CommandText = "INEBA.STD_PKG.SPR_PATIENTS_UPDATE"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Append cmd.CreateParameter("O_ERRCODE", adNumeric, adParamInput, 0, gUtils.Data2V(o_ERRCODE))
cmd.Parameters.Append cmd.CreateParameter("I_ID", adNumeric, adParamInput, 0, gUtils.Data2V(i_ID))
cmd.Parameters.Append cmd.CreateParameter("I_FIRSTNAME", adVarChar, adParamInput, 50, gUtils.Data2V(i_FIRSTNAME))
cmd.Parameters.Append cmd.CreateParameter("I_LASTNAME", adVarChar, adParamInput, 50, gUtils.Data2V(i_LASTNAME))
cmd.Parameters.Append cmd.CreateParameter("I_DOCTYPE", adVarChar, adParamInput, 10, gUtils.Data2V("DNI"))
cmd.Parameters.Append cmd.CreateParameter("I_DOCNUM", adVarChar, adParamInput, 20, gUtils.Data2V(i_DOCNUM))
cmd.Parameters.Append cmd.CreateParameter("I_EMAIL", adVarChar, adParamInput, 100, gUtils.Data2V(i_EMAIL))
cmd.Parameters.Append cmd.CreateParameter("I_BIRDTHDATE", adDBTimeStamp, adParamInput, 0, gUtils.Data2V(i_BIRDTHDATE))
cmd.Parameters.Append cmd.CreateParameter("I_SEX", adVarChar, adParamInput, 1, gUtils.Data2V(i_SEX))
cmd.Parameters.Append cmd.CreateParameter("I_COUNTRY", adNumeric, adParamInput, 0, gUtils.Data2V(i_COUNTRY))
cmd.Parameters.Append cmd.CreateParameter("I_CITY", adVarChar, adParamInput, 50, gUtils.Data2V(i_CITY))
cmd.Parameters.Append cmd.CreateParameter("I_ADDRESS", adVarChar, adParamInput, 100, gUtils.Data2V(i_ADDRESS))
cmd.Parameters.Append cmd.CreateParameter("I_ZIPCODE", adVarChar, adParamInput, 10, gUtils.Data2V(i_ZIPCODE))
cmd.Parameters.Append cmd.CreateParameter("I_PHONENUMBER", adVarChar, adParamInput, 50, gUtils.Data2V(i_PHONENUMBER))
cmd.Parameters.Append cmd.CreateParameter("I_DESCRIPTION", adVarChar, adParamInput, 200, gUtils.Data2V(i_DESCRIPTION))
cmd.Parameters.Append cmd.CreateParameter("I_STATE", adNumeric, adParamInput, 0, gUtils.Data2V(i_STATE))
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Conn.BeginTrans
'On Error GoTo e
rs.Open cmd
o_ERRCODE = cmd.Parameters(0).Value
if o_ERRCODE<>0 then
Conn.RollbackTrans
exit function
end if
Conn.CommitTrans
Set SPR_PATIENTS_UPDATE = rs
Exit Function
e:
Debug.Print "e"
Err.Raise Err.Number, Err.source, Err.Description, Err.HelpFile, Err.HelpContext
End Function
And the SQL code is:
PROCEDURE spr_patients_update
(
o_errcode IN NUMBER
,i_id IN patients.id%TYPE
,i_firstname IN patients.firstname%TYPE
,i_lastname IN patients.lastname%TYPE
,i_doctype IN patients.doctype%TYPE
,i_docnum IN patients.docnum%TYPE
,i_email IN patients.email%TYPE
,i_birdthdate IN patients.birdthdate%TYPE
,i_sex IN patients.sex%TYPE
,i_country IN patients.country%TYPE
,i_city IN patients.city%TYPE
,i_address IN patients.address%TYPE
,i_zipcode IN patients.zipcode%TYPE
,i_phonenumber IN patients.phonenumber%TYPE
,i_description IN patients.description%TYPE
,i_state IN patients.state%TYPE
,o_cursor OUT GenCursor
) IS
RaiseMode NUMBER := o_errcode;
BEGIN
UPDATE patients SET
id = i_id
,firstname = i_firstname
,lastname = i_lastname
,doctype = i_doctype
,docnum = i_docnum
,email = i_email
,birdthdate = i_birdthdate
,sex = i_sex
,country = i_country
,city = i_city
,address = i_address
,zipcode = i_zipcode
,phonenumber = i_phonenumber
,description = i_description
,state = i_state
,LASTMODIFICATIONDATE = sysdate
WHERE id = i_id ;
RaiseMode := 1; /* SET RaiseMode = True for next call*/
spr_patients_get(RaiseMode,i_id,o_cursor);
COMMIT;
o_errcode := 0;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK ;
o_errcode := SQLCODE;
IF RaiseMode > 0 THEN
RAISE ;
END IF;
END spr_patients_update;
|
|
|
|
| Re: cannot ROLLBACK in a distributed transaction [message #38976 is a reply to message #37009] |
Mon, 03 June 2002 06:27 |
Sandro Rosso
Messages: 1
Registered: June 2002
|
Junior Member |
|
|
Calling an Oracle Stored Procedure I received an error like this: "ORA-02074: cannot SET SAVEPOINT in a distributed transaction". This is not exactly your error but I think the cause should be the same.
The Oracle OLE DB Provider doesn't support nested transactions (one in your VB code and another one inside the Oracle SP).
I workaround the problem using the ODBC Ole DB Provider with specific ODBC connection or, maybe better, using the Microsoft OLE DB Provider for Oracle!!!. They should be work fine.
Good work.
Sandro Rosso
|
|
|
|
| Re: cannot ROLLBACK in a distributed transaction [message #39976 is a reply to message #37009] |
Thu, 29 August 2002 13:39 |
Greg VanHare
Messages: 1
Registered: August 2002
|
Junior Member |
|
|
I have a work around.
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
|
|
|
|
Goto Forum:
Current Time: Wed May 08 15:42:04 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
