Set up default "alter session" for an Oracle user

Question

For a JDBC application I need to issue a few ALTER SESSION commands. I do not want to put those into the application code itself. Is there a way to specify defaults for the session parameters for the database schema that the application uses (on the database side) ?

Answer 1

I haven't tested this, but could you make the application call a stored procedure that set the session variables whenever a session is created? Then, you could modify the stored procedure on the server side when needed.

Answer 2

Hi Thilo,

most session parameters are defined by the client application. If you want to override the client settings you could create a DATABASE TRIGGER. For example, this will create a LOGON trigger on the BAR schema:

CREATE OR REPLACE TRIGGER bar.foo
   AFTER LOGON ON DATABASE WHEN (USER = 'BAR')
BEGIN
   dbms_session.set_nls('NLS_NUMERIC_CHARACTERS', '''.,''');
   EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA=hr';
END foo;