How to schedule one time executable job in Oracle?-CodePudding

I want to schedule a job that will execute only once; example on 01/01/2023 00:00:00. It should not repeat again. This job will call a program with a stored procedure that will update some tables.

I have written the below code by referring the answers of this question. It is not working when I set the end_date as the same date with different time. Is repeat_interval mandatory?

-- Procedure
CREATE OR REPLACE PROCEDURE P_INSURANCE_DEACTIVATION
IS
BEGIN
    UPDATE SCHEME SET SCC_STATUS = 'N', US_CODE = 'D001' WHERE SC_CODE = 'N013';

    UPDATE INSURANCE SET INC_STATUS = 'N', US_CODE = 'D001' WHERE IN_CODE = 'N007';
    
    COMMIT;
END;

-- Schedule
BEGIN
 DBMS_SCHEDULER.CREATE_SCHEDULE (
 schedule_name   => 'SCH_INSURANCE_DEACT',
 start_date    => TO_DATE('22-12-2022 18:05:00','DD-MM-YYYY HH24:MI:SS'),
 repeat_interval  => 'FREQ=MINUTELY; INTERVAL=1; ',  
 end_date     => TO_DATE('22-12-2022 18:07:00','DD-MM-YYYY HH24:MI:SS'),
 comments     => 'Only once');
END;

-- Scheduled Program
BEGIN
DBMS_SCHEDULER.CREATE_PROGRAM (
 program_name      => 'PROG_INSURANCE_DEACT',
 program_action     => 'P_INSURANCE_DEACTIVATION',
 program_type      => 'STORED_PROCEDURE');
END;

-- Scheduled Job
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
  job_name     => 'JOB_INSURANCE_DEACT',
  program_name   => 'PROG_INSURANCE_DEACT',
  schedule_name   => 'SCH_INSURANCE_DEACT');
END;

exec dbms_scheduler.enable('JOB_INSURANCE_DEACT’)

CodePudding user response：

After looking at the documentation for DBMS_SCHEDULER, you can see that you can create a scheduled job without needing to define a schedule as long as you set a start time.

Personally, I wouldn't bother creating a procedure and program since the code being executed is so simple and it is just a one-time job.

The code below can be used to create a job that will run at midnight on Jan 1st 2023, but you might need to adjust the time zone for your scenario since midnight is at a different time depending which time zone you are in.

BEGIN
    DBMS_SCHEDULER.create_job (job_name     => 'JOB_INSURANCE_DEACT',
                               job_type     => 'PLSQL_BLOCK',
                               job_action   => q'[BEGIN
    UPDATE SCHEME SET SCC_STATUS = 'N', US_CODE = 'D001' WHERE SC_CODE = 'N013';

    UPDATE INSURANCE SET INC_STATUS = 'N', US_CODE = 'D001' WHERE IN_CODE = 'N007';
    
    COMMIT;
END;]',
                               start_date   => TIMESTAMP '2023-01-01 00:00:00 -05:00',
                               enabled      => TRUE);
END;

As an additional note, you do not need to have a COMMIT at the end of your code being executed by a job. When a job completes, it will automatically commit.