Home » SQL & PL/SQL » SQL & PL/SQL » How to scheduling a job from 8 am to 8 pm (Oracle 10g)
How to scheduling a job from 8 am to 8 pm [message #594594] Fri, 30 August 2013 11:03 Go to next message
sss111ind
Messages: 428
Registered: April 2012
Location: India
Senior Member

Hi All,

I want to create a scheduler which should run from 8 am to 8 pm and every half an hour.How to do that.
I tried dbms_scheduler because my procedure accepts two parameters.

 DBMS_SCHEDULER.CREATE_SCHEDULE (
    schedule_name   => 'in_program_schedule',
    start_date      => trunc(sysdate)+(1/3),-- it is not taking
    repeat_interval => 'freq=minutely; interval=30; bysecond=0;',
    END_DATE        => trunc(sysdate)+(5/6),-- it is not taking
    comments        => 'Repeats every half an hour');


Please help.

Regards,
Nathan

[Updated on: Fri, 30 August 2013 11:04]

Report message to a moderator

Re: How to scheduling a job from 8 am to 8 pm [message #594595 is a reply to message #594594] Fri, 30 August 2013 11:10 Go to previous messageGo to next message
BlackSwan
Messages: 21978
Registered: January 2009
Senior Member
"it is not taking" while likely true, it is 100% devoid of any actionable detail.

One possible solution is to run a procedure every 30 minutes & have the procedure it self check if the current time is before 8AM or after 8PM.
If so, then EXIT & do nothing.

How can reproduce what you report?
Re: How to scheduling a job from 8 am to 8 pm [message #594597 is a reply to message #594595] Fri, 30 August 2013 11:18 Go to previous messageGo to next message
sss111ind
Messages: 428
Registered: April 2012
Location: India
Senior Member

But do not you think that is unnecessary because the job is running and nothing is really happening .
I tried with below one but it is for one hour not half an hour.
 DBMS_SCHEDULER.CREATE_SCHEDULE (
    schedule_name   => 'in_program_schedule',
    start_date      => SYSDATE,
    repeat_interval => 'freq=daily; byhour=8,9,10,11,12,13,14,15,16,17,18,19,20; interval=30; bysecond=0;',
    END_DATE        => null ,
    comments        => 'Repeats every half an hour');

Re: How to scheduling a job from 8 am to 8 pm [message #594600 is a reply to message #594594] Fri, 30 August 2013 11:29 Go to previous messageGo to next message
Michel Cadot
Messages: 57648
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Did you assign the schedule to the job?

Regards
Michel
Re: How to scheduling a job from 8 am to 8 pm [message #594603 is a reply to message #594600] Fri, 30 August 2013 11:36 Go to previous messageGo to next message
BlackSwan
Messages: 21978
Registered: January 2009
Senior Member
>But do not you think that is unnecessary because the job is running and nothing is really happening .

we only know what you post.
we have no evidence of anything; only your unsubstantiated claims of what is & is not happening.

You have a mystery & you have provided us no clues.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: How to scheduling a job from 8 am to 8 pm [message #594606 is a reply to message #594600] Fri, 30 August 2013 11:43 Go to previous messageGo to next message
sss111ind
Messages: 428
Registered: April 2012
Location: India
Senior Member

Yes sir I assigned.


BEGIN
 DBMS_SCHEDULER.CREATE_PROGRAM (
    program_name        => 'in_pr_program',
    PROGRAM_TYPE        => 'STORED_PROCEDURE',
    program_action      => 'insert_records',
    number_of_arguments => 2,
    ENABLED             => FALSE,
    comments            => 'Program to insert records into the base table from other table.');

  DBMS_SCHEDULER.define_program_argument (
    PROGRAM_NAME      => 'in_pr_program',
    argument_name     => 'p_param1',
    argument_position => 1,
    ARGUMENT_TYPE     => 'VARCHAR2',
    default_value     => '');
 
  DBMS_SCHEDULER.define_program_argument (
    PROGRAM_NAME      => 'in_pr_program',
    argument_name     => 'p_param2',
    argument_position => 2,
    ARGUMENT_TYPE     => 'VARCHAR2',
    default_value     => '');
    
 DBMS_SCHEDULER.ENABLE (NAME => 'in_pr_program');

    DBMS_SCHEDULER.CREATE_SCHEDULE (
    schedule_name   => 'in_pr_schedule',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'freq=minutely; interval=30; bysecond=0;',
    END_DATE        => NULL,
    comments        => 'Repeats every half an hour');

 DBMS_SCHEDULER.CREATE_JOB (
    job_name      => 'in_pr_job',
    program_name  => 'in_pr_program',
    schedule_name => 'in_pr_schedule',
    ENABLED       => TRUE,
    comments      => 'Job defined for pulling records.');
END;

[Updated on: Fri, 30 August 2013 11:45]

Report message to a moderator

Re: How to scheduling a job from 8 am to 8 pm [message #594608 is a reply to message #594606] Fri, 30 August 2013 12:28 Go to previous messageGo to next message
Michel Cadot
Messages: 57648
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
nothing is really happening


As BlackSwan said, what does this mean? What is or is not happening?

Regards
Michel
Re: How to scheduling a job from 8 am to 8 pm [message #594613 is a reply to message #594597] Fri, 30 August 2013 13:27 Go to previous messageGo to next message
John Watson
Messages: 4102
Registered: January 2010
Location: Global Village
Senior Member
sss111ind wrote on Fri, 30 August 2013 17:18
But do not you think that is unnecessary because the job is running and nothing is really happening .
I tried with below one but it is for one hour not half an hour.
 DBMS_SCHEDULER.CREATE_SCHEDULE (
    schedule_name   => 'in_program_schedule',
    start_date      => SYSDATE,
    repeat_interval => 'freq=daily; byhour=8,9,10,11,12,13,14,15,16,17,18,19,20; interval=30; bysecond=0;',
    END_DATE        => null ,
    comments        => 'Repeats every half an hour');


This,
repeat_interval => 'freq=daily; byhour=8,9,10,11,12,13,14,15,16,17,18,19,20; interval=30; bysecond=0;',
creates a schedule that repeats every 30 days. The INTERVAL refers back to the FREQ, so you probably need to specify freq=minutely and interval=30
Re: How to scheduling a job from 8 am to 8 pm [message #594626 is a reply to message #594613] Sat, 31 August 2013 06:38 Go to previous message
sss111ind
Messages: 428
Registered: April 2012
Location: India
Senior Member

Ok thanks All like everytime,

I think this will work.
 DBMS_SCHEDULER.CREATE_SCHEDULE (
    schedule_name   => 'in_program_schedule',
    start_date      => SYSDATE,
   repeat_interval => 'freq=minutely;  byhour=8,9,10,11,12,13,14,15,16,17,18,19,20; byminute=00,30; bysecond=0;',
    END_DATE        => null ,
    comments        => 'Repeats every half an hour between 8 am to 8 pm');

[Updated on: Sat, 31 August 2013 06:38]

Report message to a moderator

Previous Topic: Read csv File from Shared Location
Next Topic: What is the Worng in this Code
Goto Forum:
  


Current Time: Thu Apr 24 14:52:55 CDT 2014

Total time taken to generate the page: 0.09925 seconds