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.
Ensure you begin a transaction first and use the same EntityManager for the setting and query.