Convert DBMS_JOB to DBMS_SCHEDULER by example

=========================
General
=========================
A task - convert existing Jobs to Scheduler.
First part of the post - step by step, errors, and fixes
Second part of the post: "smooth" example - grants and PL/SQL code

=========================
Part 1 - step by step, errors, and fixes
=========================

1. Try to create DBMS_SCHEDULER Job.

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name            =>  'JOB_MY_PACKAGE.my_procedure',
    job_type             => 'PLSQL_BLOCK',
    job_action           => 'MY_PACKAGE.my_procedure;',
    start_date           => SYSDATE +1,
    repeat_interval      => 'FREQ=DAILY',
    end_date             => NULL,
    enabled              => TRUE,
    comments             => 'Converted From Job');
  COMMIT;   
END; 
/
ORA-27486: insufficient privileges
ORA-06512: at "SYS.DBMS_ISCHED", line 175
ORA-06512: at "SYS.DBMS_SCHEDULER", line 288
ORA-06512: at line 11
ORA-06512: at line 11

2. As system - Grant missing priveledges:
GRANT CREATE JOB TO MY_USER;
GRANT SCHEDULER_ADMIN TO MY_USER;

3. Try again to create the job

BEGIN

  DBMS_SCHEDULER.CREATE_JOB (

    job_name            =>  'JOB_MY_PACKAGE.my_procedure',

    job_type             => 'PLSQL_BLOCK',

    job_action           => 'MY_PACKAGE.my_procedure;',

    start_date           => SYSDATE +1,

    repeat_interval      => 'FREQ=DAILY',

    end_date             => NULL,

    enabled              => TRUE,

    comments             => 'Converted From Job');

  COMMIT;    

END;   

/

ERROR at line 1:

ORA-23306: schema SUPPORT_UTIL_PKG does not exist

ORA-06512: at "SYS.DBMS_ISCHED", line 175

ORA-06512: at "SYS.DBMS_SCHEDULER", line 288

ORA-06512: at line 2

4. Need to remove ';' from job_action value.
    Need to remove '.' from job_name

BEGIN

  DBMS_SCHEDULER.CREATE_JOB (

    job_name            =>  'JOB_MY_PACKAGE_my_procedure',

    job_type             => 'PLSQL_BLOCK',

    job_action           => 'MY_PACKAGE.my_procedure',

    start_date           => SYSDATE +1,

    repeat_interval      => 'FREQ=DAILY',

    end_date             => NULL,

    enabled              => TRUE,

    comments             => 'Converted From Job');

  COMMIT;    

END;   

/

Completed Successfully

5. To see the job details:

SELECT * FROM USER_SCHEDULER_JOBS;

=========================
Part 2 - "smooth" example - grants and PL/SQL code
=========================

As system
GRANT CREATE JOB TO MY_USER;
GRANT SCHEDULER_ADMIN TO MY_USER;

In PL/SQL code:

PROCEDURE convertJobsToScheduler  IS

  v_job_name         VARCHAR2(1000);
  v_job_action       VARCHAR2(1000);
  v_module_name      SGA_W_LOG.procedure_name%TYPE;
  v_msg_str          SGA_W_LOG.data%TYPE;

  CURSOR jobs_cur  IS
  SELECT * FROM USER_JOBS
  WHERE 1=1
    AND UPPER(what) NOT LIKE '%OBSOLETE_JOB%';

BEGIN
  v_module_name := 'DBA_PKG.convertJobsToScheduler';
  v_msg_str := 'Starting';
  write_sga_w_log(v_module_name,v_msg_str);

  FOR jobs_rec IN jobs_cur LOOP
    
    v_job_action := REPLACE(jobs_rec.what,';','');
    v_job_name := 'JOB_'|| UPPER(SUBSTR(v_job_action,1,INSTR(v_job_action,'.')))||LOWER(SUBSTR(v_job_action,1+INSTR(v_job_action,'.')));

    v_job_name := REPLACE (v_job_name,'.','_');

  

    BEGIN
      DBMS_SCHEDULER.CREATE_JOB (
        job_name             =>  v_job_name,
        job_type             => 'STORED_PROCEDURE',
        job_action           => v_job_action,
        start_date           => jobs_rec.next_date,
        repeat_interval      => 'FREQ=DAILY',
        end_date             =>  NULL,
        enabled              =>  TRUE,
        auto_drop            =>  FALSE,
        comments             => 'Converted From Job' );
      
      --DBMS_JOB.remove(jobs_rec.job);
        DBMS_JOB.next_date(jobs_rec.job, TO_DATE('30000101','YYYYMMDD'));
      COMMIT;
      
    END;      
  END LOOP;  

  v_msg_str := 'Finished Successfully.';
  write_sga_w_log(v_module_name,v_msg_str);

EXCEPTION
  WHEN OTHERS THEN
    v_msg_str := 'Failed!!! Unexpected Error '||SQLERRM;
    write_sga_w_log(v_module_name,v_msg_str);
    RAISE;
END convertJobsToScheduler;

SELECT * FROM USER_SCHEDULER_JOBS;

=========================
General
=========================
To Run Scheduler Job now:

BEGIN

  DBMS_SCHEDULER.run_job('REPORT_PKG_day_report');

END;

/

-- Job is executes

Now check start_date

SELECT JOB_NAME, START_DATE FROM USER_SCHEDULER_JOBS;

JOB_NAME                   START_DATE

-------------------------- ------------------------------------

REPORT_PKG_day_report      04-AUG-21 01.00.00.000000 AM +08:00

=========================
General
=========================
See job schedule:

SELECT * FROM DBA_SCHEDULER_JOBS 

WHERE job_name = 'PURGE_LOG';

SELECT * FROM DBA_SCHEDULER_SCHEDULES 

WHERE schedule_name = 'DAILY_PURGE_SCHEDULE';

Set job to run at 04:00

BEGIN

  DBMS_SCHEDULER.SET_ATTRIBUTE (

   name         =>  'DAILY_PURGE_SCHEDULE',

   attribute    =>  'repeat_interval',

   value        =>  'freq=daily;byhour=4;byminute=0;bysecond=0');

END;

/

Set job to run at X time

BEGIN DBMS_SCHEDULER.SET_ATTRIBUTE(

   name         =>  'DAILY_PURGE_SCHEDULE',

   attribute    =>  'start_date',

   value        =>  TO_TIMESTAMP_TZ('18-10-2021 12:00:00 AM +11','DD-MM-YYYY HH:MI:SS AM TZR'));

END;

/