Where is oracle session timezone set

oracleoracle-11g-r2

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

Best Answer

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.

Related Solutions

Timeout for a query over database link

Since you're using dbms_scheduler, you can set the max_run_duration attribute of the job to some limit, and then have the scheduler email you if that event is raised. Behind the scenes Oracle uses queueing tables (which can enable you to create jobs that fire when an event is raised, if you wanted to take the extra steps to do more automation around your response). But basically any job that runs over the max_run_duration you setup will raise event type: JOB_OVER_MAX_DUR

The email piece is built in 11gr2, see here for good writeup.

Hope that helps.

How to embed a sub-prodecure call in a SELECT statement in an Oracle 11g PL/SQL stored procedure

It wouldn't perform as well, but to meet your requirements given your limitations, you could use a pipelined result set. Both your original procedure and your new procedure could then use the function. The original one would return the entire results, and the new procedure would wrap the function in a larger query that does the GROUP BY. In this way the core query code would not be duplicated. Here is a pipelined result set example along with two queries using it:

create or replace PACKAGE Example AUTHID Definer AS

Type tRow Is Record (
     F1 Date
   , F2 Date
   );
Type tTable Is Table of tRow;
Function PipelineResult(pDate In Date DEFAULT sysdate) Return tTable Pipelined;

END Example;
/


create or replace PACKAGE BODY Example AS

Function PipelineResult(pDate In Date DEFAULT sysdate) Return tTable Pipelined AS
   vYesterday Date := trunc(sysdate-1);
Begin
  For vRow IN (
      SELECT pDate, vYesterday FROM DUAL
      UNION ALL
      SELECT pDate, vYesterday-1 FROM DUAL
      UNION ALL
      SELECT pDate+1, vYesterday FROM DUAL
  )
  Loop
    Pipe Row(vRow);
  End Loop;
End;

END Example;
/


SELECT * FROM TABLE(Example.PipelineResult);

SELECT F1, count(*) FROM TABLE(Example.PipelineResult) GROUP BY F1;

Related Question