0

I was using the following SQL in PostgreSQL to set a local variable

SET LOCAL myKey = 'myValue'

and then I was able to retrieve this value from a trigger for example - in my case I was passing a username and used by a trigger to log it in an audit table when a row was deleted.

current_setting('myKey')

Now, I am trying to achieve something similar in Oracle 19c and one way proposed is the following:

 EXEC DBMS_SESSION.SET_CONTEXT('CLIENTCONTEXT', 'myKey', 'myValue');

and then retrieve it like this:

 SELECT SYS_CONTEXT('CLIENTCONTEXT', 'myKey') FROM dual;
  • Is this the best practice?
  • Are there any better alternatives?
  • Is this transaction-safe? I am using JDBC and a pool of connections is used.
georgeliatsos
  • 1,168
  • 3
  • 15
  • 34
  • 1
    What is a purpose of this? Session context is really the only option to set a global variable for SQL engine. The value persists even after commit. There are ways how to store them on borrow from connection pool (at least in EclipseLink). – ibre5041 Feb 24 '22 at 14:18
  • "Transaction-safe" would be to store it in a global temporary table with delete on commit. You can create your own package with setter and getters for that – Sayan Malakshinov Feb 25 '22 at 02:11
  • What does transaction-safe mean here? A transaction is always inside one session. The variable you are setting is valid for its own session. Other sessions don't see it. But you can set the variable whenever you like, e.g. set_context, insert into a, set_context, insert into b, commit. Then the variable changed within the transaction. It was one value when inserting into a and another when inserting into b. Does this interfere with what you call transaction-safe? – Thorsten Kettner Feb 25 '22 at 14:57
  • I have wrongly stated it when I said transaction-safe. What I am trying to understand is that if two application users simultaneous execute set_context will this parallel case cause any problem? – georgeliatsos Feb 25 '22 at 16:57

0 Answers0