2 Replies Latest reply: Dec 28, 2012 3:06 AM by user503635 RSS

    Create_Scheduler Usage.

    933257
      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
          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
            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