How Can I Get Time Zone Region From Sqlplus?

I am using Oracle Developer and Oracle 11g (Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production) select SESSIONTIMEZONE from DUAL; Result in SQLPLUS/system_conf

Solution 1:

I assume SQL Developer makes an implict ALTER SESSION SET time_zone=... when you open a new connection.

I see several solutions:

• Define environment variable TZ on client host
• Create a database trigger and set timezone accordingly
• Change the timezone of the database - works unless any user sets his personal timezone

Database Trigger:

CREATEOR REPLACE TRIGGER LOG_T_LOGON 
   AFTER LOGON ON DATABASE
DECLAREBEGIN
   IF ora_login_user ISNULLTHENRETURN;
   END IF;
   IF ora_login_user IN (...) THEN-- Prevent to be executed for EACH userexecute immediate 'ALTER SESSION SET time_zone = ''America/New_York''';
   END IF;
END;

END;

Solution 2:

You could write a function in your application or in this trigger like this:

IF SESSIONTIMEZONE ='-4:00'THENexecute immediate 'ALTER SESSION SET time_zone = ''America/New_York''';
ELSIF SESSIONTIMEZONE ='-5:00'THENexecute immediate ...
END IF;