CODEWITHSUNDEEP
oracle-databasejpanls-lang
Rasmus Franke
Rasmus Franke

Reputation: 4524

Setting oracle NLS_LANG for a JPA query

I need to access an oracle database view that require a certain nls_lang to be set - if it's not, an index will not be used and the database will lock up pretty much forever. I know this is bad joke design wise, but it is nothing I can change.

I can change nls_lang by setting the system variables user.country and/oruser.language, which will correctly change the nls_lang. However, this will affect my application in unknown ways, and even worse: other applications on the same server. Simply put this is not a possible solution.

So how can I set nls_lang for a single JPA native query, while keeping another locale set for the server? It needs to be a native query because I'm selecting from a view that does not contain a PK, which is required by Entities.

I've tried

entityManager.createNativeQuery("ALTER SESSION set NLS_LANGUAGE = 'American'").executeUpdate();

before running my query, but it does not seem to have effect, probably because the query is done in a different session. Perhaps in theory I could set Locale.setDefault() temporarily, and restore it after the query, but that would give concurrency problems.

Upvotes: 1

Views: 2404

Answers (1)

James
James

Reputation: 18389

Ensure you begin a transaction first and use the same EntityManager for the setting and query.

Upvotes: 1

Related Questions