Transaction guard

Getting the logical transaction id in 12c will greatly simplify your error handling and enhance your business continuity in your application.

In 11g and below, your java code use to look like

try {
insert into...
} catch () {
error_handling()
}

but one probably assumed the insert failed when it was committed (e.g. database server process core dump).

Now in 12c, you can get a logical transaction id and then later, from another session, check if that transaction was committed. Which solves quite a bunch of integrity issues (e.g. duplicate rows)

Let’s try

import java.sql.*;
import oracle.jdbc.pool.*;
import oracle.jdbc.*;

public class TG {
public static void main(String argv[]) throws
SQLException {
String url = "jdbc:oracle:thin:@(DESCRIPTION"
+"=(ADDRESS=(PROTOCOL=TCP)(Host=srv01)("
+"Port=1521))(CONNECT_DATA=(SERVICE_NAME="
+"svc01)))";
OracleDataSource ods=new OracleDataSource();
ods.setURL(url);
ods.setUser("SCOTT");
ods.setPassword("tiger");
OracleConnection conn = (OracleConnection)
ods.getConnection();
LogicalTransactionId ltxid = conn.
getLogicalTransactionId();
try {
System.out.println("Start");
conn.prepareStatement(
"insert into t values (1)").execute();
if (Math.random() > .5) {
throw new Exception();
}
System.out.println("OK");
} catch (Exception e) {
System.out.println("ERROR");
OracleConnection conn2 =
(OracleConnection) ods.getConnection();
CallableStatement c = conn2.prepareCall(
"declare b1 boolean; b2 boolean; begin "
+"DBMS_APP_CONT.GET_LTXID_OUTCOME(?,b1,"
+"b2); ? := case when B1 then "
+"'COMMITTED' else 'UNCOMMITTED' end; "
+"end;");
c.setBytes(1, ltxid.getBytes());
c.registerOutParameter(2,
OracleTypes.VARCHAR);
c.execute();
System.out.println("Status = "+
c.getString(2));
}
}
}


getLogicalTransactionId gives me a transaction id (this is internally saved in SYS.LTXID_TRANS so it survives reboots, failover and disconnections) and GET_LTXID_OUTCOME gets the outcome.

There is few preparation steps

GRANT EXECUTE ON DBMS_APP_CONT TO SCOTT;
declare PARAMETER_ARRAY dbms_service.
svc_parameter_array;
begin
PARAMETER_ARRAY('COMMIT_OUTCOME'):='true';
dbms_service.create_service(
'SVC01','TNS01',PARAMETER_ARRAY);
dbms_service.start_service('SVC01');
end;
/
CREATE TABLE SCOTT.T(x number);


Due to my Random() call, I get exceptions sometimes, but it is always commits

C:\> java TG
Start
OK

C:\> java TG
Start
ERROR
Status = COMMITTED

No need to redo the insert.

Now I dropped the table t and run the same code

SQL> drop table scott.t;

C:\>java TG
Start
ERROR
Status = UNCOMMITTED

Now it fails and I know it!