Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a logging table

CREATE TABLE EMAIL_LOGGING
(
  ID               NUMBER(9)                    NOT NULL,
  SEND_DATE        TIMESTAMP(6)                 DEFAULT LOCALTIMESTAMP        NOT NULL,
  SEND_TO_USER_ID  NUMBER(9)                    NOT NULL);

and a package that inserts into it with this fragment:

  INSERT INTO EMAIL_LOGGING ("ID",SEND_DATE, SEND_TO_USER_ID)
       VALUES (NULL, LOCALTIMESTAMP,send_to_user_in);

When this is called from a DBMS_JOB from one package it inserts Greenwich mean time. When it is called from a DBMS_JOB from another package that uses a db_link to another database it correctly inserts the local time.

I think this is caused by the client's timezone being used but adding this to the job did not resolve it:

execute immediate 'alter session set time_zone=local';

Of course the best solution is to move to DBMS_Scheduler but until that is done how can I ensure that the timestamp that is inserted is the local time?

select * from v$version where banner like 'Oracle%';
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
SELECT DBTIMEZONE FROM DUAL;
+00:00
SELECT SESSIONTIMEZONE from dual;
-04:00
share|improve this question

1 Answer 1

up vote 1 down vote accepted

I resolved this issue by setting this attribute

BEGIN
  DBMS_SCHEDULER.set_scheduler_attribute (
    attribute => 'default_timezone',
    value     => 'EST');
END;

and moving the job to DBMS_SCHEDULER. Now, when it runs the correct (local) time is entered in the logging table.

share|improve this answer
    
+1 for finding your own answer and giving enough time for others to answer (which no one else did). –  RolandoMySQLDBA May 29 '13 at 15:52

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.