OraFAQ Forum: Server Administration » Job Scheduling on Last Sun, Tue and Thu of every month

Home » RDBMS Server » Server Administration » Job Scheduling on Last Sun, Tue and Thu of every month (Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Job Scheduling on Last Sun, Tue and Thu of every month [message #425909]

Tue, 13 October 2009 01:02

NewLife
Messages: 170
Registered: April 2008

Senior Member

Hi,

I need to schedule a job to run on the Last Sunday, Last Tuesday and the Last Thursday of every month at 11:30AM.

I came up with the below block of code using dbms_scheduler, Is the below piece of code correct? Any suggestions are welcome.

begin
  dbms_scheduler.create_job(
      job_name => 'PRE_EOM_CHECK'
     ,job_type => 'STORED_PROCEDURE'
     ,job_action => 'ADM.P_JOB_TEST'
     ,start_date => to_timestamp('2009/10/25 11:30:00', 'YYYY/MM/DD HH24:MI:SS')
     ,repeat_interval => 'FREQ=MONTHLY; BYDAY=-1SUN, -1TUE, -1THU; BYHOUR=11; BYMINUTE=30
     ,enabled => TRUE
     ,comments => 'PRE-EOM Check');
end;
/

Thanks Guys Smile

[Updated on: Tue, 13 October 2009 01:09]

Report message to a moderator

Re: Job Scheduling on Last Sun, Tue and Thu of every month [message #426030 is a reply to message #425909]

Tue, 13 October 2009 18:18

ebrian
Messages: 2794
Registered: April 2006

Senior Member

Yes, your string looks correct:

SQL> DECLARE
  2  start_date        TIMESTAMP;
  3  return_date_after TIMESTAMP;
  4  next_run_date     TIMESTAMP;
  5  BEGIN
  6  start_date :=
  7    to_timestamp_tz('13-OCT-2009 10:00:00','DD-MON-YYYY HH24:MI:SS');
  8  return_date_after := start_date;
  9  FOR i IN 1..10 LOOP
 10    DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING(
 11      'FREQ=MONTHLY; BYDAY=-1SUN, -1TUE, -1THU; BYHOUR=11; BYMINUTE=30',
 12      start_date, return_date_after, next_run_date);
 13  DBMS_OUTPUT.PUT_LINE('next_run_date: ' || next_run_date);
 14  return_date_after := next_run_date;
 15  END LOOP;
 16  END;
 17  /
next_run_date: 25-OCT-09 11.30.00.000000 AM
next_run_date: 27-OCT-09 11.30.00.000000 AM
next_run_date: 29-OCT-09 11.30.00.000000 AM
next_run_date: 24-NOV-09 11.30.00.000000 AM
next_run_date: 26-NOV-09 11.30.00.000000 AM
next_run_date: 29-NOV-09 11.30.00.000000 AM
next_run_date: 27-DEC-09 11.30.00.000000 AM
next_run_date: 29-DEC-09 11.30.00.000000 AM
next_run_date: 31-DEC-09 11.30.00.000000 AM
next_run_date: 26-JAN-10 11.30.00.000000 AM

PL/SQL procedure successfully completed.

Report message to a moderator

Re: Job Scheduling on Last Sun, Tue and Thu of every month [message #426038 is a reply to message #426030]

Tue, 13 October 2009 23:22

NewLife
Messages: 170
Registered: April 2008

Senior Member

Thanks bro Smile

Report message to a moderator

Previous Topic:	unable to find the table details from tab table with DBA role (merged 7)
Next Topic:	create dimension

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Jan 12 07:45:40 CST 2025