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 220.127.116.11.0 - Production SELECT DBTIMEZONE FROM DUAL; +00:00 SELECT SESSIONTIMEZONE from dual; -04:00