How to get Oracle db transaction id (replace Postgres txid_current())

Question

I have to make a change to use Oracle Database 11g Express instead of PostgreSQL 9.5.4. We use JPA, Hibernate 3.5.0-Final, Java 8, JBoss, so we have:

entityMgr.open();
entityMgr.begin();
entityMgr.merge(action);
//entityMgr.flush();    // I have tried with and without - same result
//entityMgr.getTransaction().isActive();    // returns true
Object transactionId = entityMgr.createNativeQuery("SELECT DBMS_TRANSACTION.LOCAL_TRANSACTION_ID FROM dual").getSingleResult();
...

For some reason transactionId is always null. From what I read transactionId is null if there is no transaction, however from my understanding there is transaction running (if uncommented transaction.isActive() returns true). We are connected to Oracle db as other operations before this one are working fine. When we used Postgress before it worked properly when the last line was replaced with below:

Object transactionId = entityMgr.createNativeQuery("SELECT txid_current()").getSingleResult();

What is wrong with my code, how can I get actual transactionId in Oracle please ?

Matthew McPeak · Accepted Answer · 2018-08-17 14:45:47Z

1

DBMS_TRANSACTION.LOCAL_TRANSACTION_ID will return NULL if you have not started a transaction.

You can use DBMS_TRANSACTION.LOCAL_TRANSACTION_ID(create_transaction => TRUE) to tell it to start a new transaction if there is not already one. That way, it will never return NULL.

Unfortunately, the parameter being a BOOLEAN will not work well in your SELECT..FROM DUAL approach. You would need to create a wrapper function or something -- or use an inline PL/SQL Function, like this:

WITH FUNCTION trx_id RETURN VARCHAR2 AS 
BEGIN
  RETURN DBMS_TRANSACTION.LOCAL_TRANSACTION_ID(true);
END;
SELECT trx_id FROM DUAL;

answered Aug 17, 2018 at 14:45

Matthew McPeak

18.1k3 gold badges32 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to get Oracle db transaction id (replace Postgres txid_current())

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related