This discussion is archived
2 Replies Latest reply: Dec 28, 2012 1:06 AM by user503635 RSS

Create_Scheduler Usage.

933257 Newbie
Currently Being Moderated
Hi all,
I am using 10.2.0.4.0 version of oracle. i am supposed to migrate all the Oracle jobs(DBMS_JOBS) to SCHEDULER format in my DB.
     Below is an example of a job which runs each day at 5 AM. I have coded the start_date value such that, i can execute the script at anypoint of time in the day without hampering the job execution for the day.
     
     My question is Whether Create_schedule method can be used in this scenario? How is it better than below implementation?     
     
-- DBMS_SCHEDULER Code
DECLARE
BEGIN 
 dbms_scheduler.create_job( 
  job_name=>'P_Procedure', 
  job_type => 'PLSQL_BLOCK',
  job_action=> 'P_Procedure;', 
  start_date => CASE 
                          WHEN (to_char(sysdate,'HH24')+(to_char(sysdate,'MI')/60)+(to_char(sysdate,'SS')/3600)<=5) 
                         THEN 
                              trunc(SYSDATE)+5/24
                         ELSE 
                           TRUNC(SYSDATE+1) + (5/24)
                     END, 
  repeat_interval => 'TRUNC(SYSDATE+1) + (5/24)', 
  enabled => true, auto_drop=> false, 
  comments => 'Converted from job 123'
);
END;
  • 1. Re: Create_Scheduler Usage.
    Mihael Pro
    Currently Being Moderated
    Named schedules can be used when many jobs share them. In case of one job you can set start_date, repeat_interval and end_date for job. But instead of using complex sql expressions, you can use easy calendaring syntax.
  • 2. Re: Create_Scheduler Usage.
    user503635 Explorer
    Currently Being Moderated
    JOB_TYPE should be STORED_PROCEDURE

    try below

    DECLARE
    BEGIN
    dbms_scheduler.create_job(
    job_name=>'P_Procedure',
    job_type => 'stored_procedure',
    job_action=> 'P_Procedure;',
    start_date=> trunc(sysdate) + 5/24,
    repeat_interval => 'FREQ=DAILY; BYHOUR=5',
    enabled => true, auto_drop=> false,
    comments => 'Converted from job 123'
    );
    END;

    Edited by: user503635 on Dec 28, 2012 1:05 AM

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points