java - How to send arbitrary parameters to Oracle trigger?

Question

The purpose is to send extra information to triggers like current user id from a web application. Since a connection pool is used, and same user id is used for all connections how do I pass the original web user id to trigger? This I need to implement without touching application code. It is a java based application.

John

Answer 1

You can use the client_identifier session variable to pass an application user to a trigger.

Set it after connecting to the database like this:

  CALL dbms_session.set_identifier('<<username>>');

and retrieve it inside the trigger:

  SELECT sys_context('USERENV','CLIENT_IDENTIFIER') INTO username FROM DUAL;

More info can be found in the Oracle docs