Problem with Visual Basic Calls To Oracle Stored Procedures

From: Rory Monteith <rjmonteith_at_potashcorp.com>
Date: 22 Nov 2001 13:35:28 -0800
Message-ID: <ebeeaa46.0111221335.63acab87@posting.google.com>

Hi all,

I am having a problem with accessing certain Oracle stored procedures from Visual Basic 6.0 using ADO and the Oracle OLEDB driver.

My problem lies with accessing stored procedures that have rollbacks or savepoints within them. Anytime I call these I get the following error raised:
ORA-02074: Cannot ROLLBACK in a Distributed Transaction

My VB code looks as follows:

  Set adoCN = New ADODB.Connection
  Set adoCMD = New ADODB.Command
  adoCN.Provider = "OraOLEDB.Oracle.1"
  adoCN.Open SERVER_CONNECTION, USER_NAME, USER_PASSWORD

Set adoCMD.ActiveConnection = adoCN

adoCMD.CommandText = "{CALL XXX_TEST_5(?)}" Set adoParamError = adoCMD.CreateParameter("sErrorMessage", adBSTR, adParamOutput, 500)
adoCMD.Parameters.Append adoParamError

adoCMD.Execute

My Stored procedure in Oracle can be as simple as the following:

PROCEDURE XXX_TEST_5(errormessage out varchar2) IS   tmpVar NUMBER;
BEGIN
  tmpVar := 0;
  rollback;
EXCEPTION
  WHEN OTHERS THEN
    errormessage := sqlerrm;
END XXX_TEST_5; Any help anyone has on this would be greatly appreciated.

Thanks,
Rory Monteith
Programmer
PotashCorp Received on Thu Nov 22 2001 - 15:35:28 CST