| How to schedule the job to run evry Sunday at 12 P.M IST [message #586833] |
Tue, 11 June 2013 04:02  |
ajaykumarkona
Messages: 399
Registered: August 2010
|
Senior Member |
|
|
Hi Experts,
I want to schedule a job which will run evry Sunday at 12 P.M IST.
I have written the below script.
BEGIN
SYS.DBMS_SCHEDULER.CREATE_JOB
(
job_name => 'SERVICE_SAL_FS.SAL_MESSAGE_BUFFER_PURGE_JOB'
,start_date => SYSTIMESTAMP
,repeat_interval => 'FREQ=WEEKLY; BYDAY=SUN; BYHOUR=12;BYMINUTE=0; BYSECOND=0;'
,end_date => NULL
,job_class => 'DEFAULT_JOB_CLASS'
,job_type => 'STORED_PROCEDURE'
,job_action => 'SERVICE_SAL_FS.SAL_MESSAGE_BUFFER_PURGE_PROC'
,comments => 'Run at 12 P.M.IST every Sunday'
,enabled => TRUE
);
END;
But the server in US.
SELECT sysdate from Dual;
6/11/2013 3:58:58 AM
--But the time in India is 02:28 PM
How to convert the timezone to IST.
Please help me how to schedule the job to run evry Sunday at 12 P.M IST.
Thanks.
|
|
|
|
| Re: How to schedule the job to run evry Sunday at 12 P.M IST [message #586838 is a reply to message #586833] |
Tue, 11 June 2013 04:26  |
ajaykumarkona
Messages: 399
Registered: August 2010
|
Senior Member |
|
|
I am sending the NLS_PARAMETERS.
SELECT * FROM v$nls_parameters
PARAMETER VALUE
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_CHARACTERSET UTF8
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS CHAR
NLS_NCHAR_CONV_EXCP FALSE
Thanks.
|
|
|
|
|
|
| Re: How to schedule the job to run evry Sunday at 12 P.M IST [message #586890 is a reply to message #586833] |
Tue, 11 June 2013 08:01 |
Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
From DBMS_SCHEDULER docs:
•The calendaring syntax does not allow you to specify a time zone. Instead the Scheduler retrieves the time zone from the start_date argument. If jobs must follow daylight savings adjustments you must make sure that you specify a region name for the time zone of the start_date. For example specifying the start_date time zone as 'US/Eastern' in New York will make sure that daylight saving adjustments are automatically applied. If instead the time zone of the start_date is set to an absolute offset, such as '-5:00', daylight savings adjustments are not followed and your job execution will be off by an hour half of the year.
•When start_date is NULL, the Scheduler will determine the time zone for the repeat interval as follows:
1.It will check whether the session time zone is a region name. The session time zone can be set by either:
■Issuing an ALTER SESSION statement, for example:
SQL> ALTER SESSION SET time_zone = 'Asia/Shanghai';
■Setting the ORA_SDTZ environment variable.
2.If the session time zone is an absolute offset instead of a region name, the Scheduler will use the value of the DEFAULT_TIMEZONE Scheduler attribute. For more information, see the SET_SCHEDULER_ATTRIBUTE Procedure.
3.If the DEFAULT_TIMEZONE attribute is NULL, the Scheduler will use the time zone of systimestamp when the job or window is enabled.
So use:
BEGIN
SYS.DBMS_SCHEDULER.CREATE_JOB(
job_name => 'SAL_MESSAGE_BUFFER_PURGE_JOB',
start_date => SYSTIMESTAMP AT TIME ZONE 'ASIA/CALCUTTA',
repeat_interval => 'FREQ=WEEKLY; BYDAY=SUN; BYHOUR=12;BYMINUTE=0; BYSECOND=0;',
end_date => NULL,
job_class => 'DEFAULT_JOB_CLASS',
job_type => 'STORED_PROCEDURE',
job_action => 'SERVICE_SAL_FS.SAL_MESSAGE_BUFFER_PURGE_PROC',
comments => 'Run at 12 P.M.IST every Sunday',
enabled => TRUE
);
END;
/
SY.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
