Devart Forums

Posted: **Thu 28 Mar 2019 00:18**

Hi,
I have a problem with nested transactions with Oracle 12.2c driver version 6.10.15.0
For example:
I call function BeginTransaction from the SQLConnection. then I want to start a nested transaction then get an error Transaction is already active, but for example, when I use driver 4.20.0.8, the error does not appear.
Can anyone help, please?

I wrote a simple example with two buttons which start the new transactions, firstly I click one button then second. and got an error:

Code:

Code: Select all

unit uMain;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, cxGraphics, cxLookAndFeels, cxLookAndFeelPainters, Menus, StdCtrls, cxButtons,uDM,DBXCommon;

type
  TfMain = class(TForm)
    btnStartFirstTransaction: TcxButton;
    btnStartSecondTransaction: TcxButton;
    procedure btnStartFirstTransactionClick(Sender: TObject);
    procedure btnStartSecondTransactionClick(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  fMain: TfMain;

implementation

{$R *.dfm}

procedure TfMain.btnStartFirstTransactionClick(Sender: TObject);
begin
fDM.MainConnection.BeginTransaction;

end;

procedure TfMain.btnStartSecondTransactionClick(Sender: TObject);
begin
fDM.MainConnection.BeginTransaction;
end;
end.

Code: Select all

unit uDM;

interface

uses
  SysUtils, Classes, WideStrings, DB, SqlExpr;

type
  TfDM = class(TDataModule)
    MainConnection: TSQLConnection;
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  fDM: TfDM;

implementation

{$R *.dfm}

end.

settings of the connection:

Looks like a bug, I thought that databases support nested transactions.
Thanks.

Posted: **Fri 26 Jul 2019 07:57**

Oracle Database doesn't support nested transactions. To implement the necessary behavior, you can try to use SAVEPONT. See this page for details: https://docs.oracle.com/database/121/CN ... m#CNCPT016

Posted: **Fri 26 Jul 2019 09:20**

MaximG, thanks for your answer,
But why with the previous version of the driver(4.20.0.8) this error does not appear? it was some kind of fix? Or requirements from the new version of Oracle Database?

Posted: **Fri 09 Aug 2019 13:31**

We couldn't reproduce the issue by following the above steps in our test environment. Please try checking the behavior of the latest dbExpress driver for Oracle version 6.11.16 by creating a new project and executing the following code snippet:

Code: Select all

var
  SQLConnection: TSQLConnection;
begin
  SQLConnection := TSQLConnection.Create(nil);
  try
    SQLConnection.DriverName := 'DevartOracleDirect';
    SQLConnection.Params.Values['User_Name'] := 'scott';
    SQLConnection.Params.Values['Password'] := 'tiger';
    SQLConnection.Params.Values['DataBase'] := '<your Oracle Sevrver>:1521:<your Service Name>';
    SQLConnection.Connected := True;
    SQLConnection.BeginTransaction;
    SQLConnection.BeginTransaction;
    SQLConnection.BeginTransaction;
  finally
    SQLConnection.Free;
  end;
end;
...

Please let us know the result.

Devart Forums

Nested Transactions -Oracle

Nested Transactions -Oracle

Re: Nested Transactions -Oracle

Re: Nested Transactions -Oracle

Re: Nested Transactions -Oracle