also with ODP in 12c, you can check the commit outcome as in jdbc
let’s create a table with a deferred primary key
create table t (x number primary key deferrable initially deferred);
Here an interactive Powershell Demo
PS> [Reflection.Assembly]::LoadFile("C:\oracle\product\12.1.0\dbhome_1\ODP.NET\bin\4\Oracle.DataAccess.dll")
GAC Version Location
--- ------- --------
True v4.0.30319 C:\Windows\Microsoft.Net\assembly\GAC_64\Oracle.DataAccess\v4.0_4.121.1.0__89b483f429c47342\Oracle.DataAccess.dll
I first load the assembly. Some of my frequent readers may prefer Load(“Oracle.DataAccess, Version=4.121.1.0, Culture=neutral, PublicKeyToken=89b483f429c47342”) rather than hardcoding the oracle home directory.
PS> $connection=New-Object Oracle.DataAccess.Client.OracleConnection("Data Source=DB01; User Id=scott; password=tiger")
create the connection
PS> $connection.open()
connect
PS> $cmd = new-object Oracle.DataAccess.Client.OracleCommand("insert into t values (1)",$connection)
prepare the statement
PS> $txn = $connection.BeginTransaction()
begin transaction
PS> $ltxid = ($connection.LogicalTransactionId -as [byte[]])
Here I have my logical transaction id. Whatever happends to my database server, crash, switchover, restore, core dump, network disconnection, I have a logical id, and I will check it later.
PS> $cmd.executenonquery()
1
One row inserted
PS> $connection2=New-Object Oracle.DataAccess.Client.OracleConnection("Data Source=DB01; User Id=scott; password=tiger")
PS> $connection2.open()
I create a second connection to monitor the first one. Monitoring your own session would be too much unsafe and is not possible.
PS> $txn.Commit()
Commit, no error.
PS> $connection2.GetLogicalTransactionStatus($ltxid)
Committed UserCallCompleted
--------- -----------------
True True
It is committed. I see it Committed from $connection2. This is what I expected.
Because I have a primary key, let’s retry and see what happend.
PS> $txn = $connection.BeginTransaction()
PS> $ltxid = ($connection.LogicalTransactionId -as [byte[]])
PS> $cmd.executenonquery()
1
PS> $txn.Commit()
Exception calling "Commit" with "0" argument(s): "ORA-02091: Transaktion wurde zurückgesetzt
ORA-00001: Unique Constraint (SCOTT.SYS_C004798) verletzt"
At line:1 char:1
+ $txn.Commit()
+ ~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : OracleException
PS> $connection2.GetLogicalTransactionStatus($ltxid)
Committed UserCallCompleted
--------- -----------------
False False
The commit fails, and from the connection2 we see it is not committed. It is a huge step toward integrity, as Oracle tells you the outcome of the transaction.
We see Committed=False.