1 Reply Latest reply on Dec 27, 2012 3:33 PM by Srini Chavali-Oracle

    dbms_scheduler.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.

      i want to know, Whether Create_schedule method can be used in this scenario? How is Create_Scheduler implementation will be 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;