CODEWITHSUNDEEP
sqloracle-databasesessionparameters
odysseus
odysseus

Reputation: 15

Oracle Session Parameter restricted to a User

In Oracle database, is there a way to restrict a "session parameter" to my user ID only? (or any other means of restriction)

Typically, using Oracle SQL Developer, I can do the "ALTER SESSION SET param=value;" and I can confirm that the parameter took effect.

But outside of SQL Developer, I want my local application client to have that same session parameter value as well. I can't find a way to do it via the application.

Is there a way to do this when connecting to Oracle database? And how to do it?

Thanks.

Upvotes: 0

Views: 923

Answers (2)

Christopher Jones
Christopher Jones

Reputation: 10496

What are the actual parameters you want to use? Some can be set via environment variables if you don't like having logon triggers that run ALTER SESSION (I recall Oracle's Tom Kyte not liking these triggers). For example NLS_DATE_FORMAT can be set as an environment variable (note if you set this, you need to also set NLS_LANG).

Another common, efficient way to set 'session state' in applications that use Oracle driver connection pools is to use a 'session callback' for example in Python:

# Set the NLS_DATE_FORMAT for a session
def initSession(connection, requestedTag):
    cursor = connection.cursor()
    cursor.execute("ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI'")

# Create the pool with session callback defined
pool = cx_Oracle.SessionPool("hr", userpwd, "orclpdb1",
                     sessionCallback=initSession, encoding="UTF-8")

# Acquire a connection from the pool (will always have the new date format)
connection = pool.acquire()

If you do decide to use ALTER SESSION, note that multiple parameters can be set in the one command: alter session set nls_date_format = 'YYYY-MM-DD' nls_language = AMERICAN

Upvotes: 0

MT0
MT0

Reputation: 167774

Use a logon trigger:

CREATE OR REPLACE TRIGGER CHANGE_DATE_FORMAT
AFTER LOGON ON DATABASE
CALL DBMS_SESSION.SET_NLS('NLS_DATE_FORMAT','"YYYY-MM-DD HH24:MI:SS"')
/

or alter the system settings:

ALTER SYSTEM SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS' SCOPE=BOTH;

Upvotes: 2

Related Questions