How to schedule one time executable job in Oracle?

Question

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’)

EJ Egyed · Accepted Answer · 2022-12-22T18:31:56.380

1

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.

edited Dec 22 '22 at 18:31

answered Dec 22 '22 at 18:18

EJ Egyed

5,791
1
8
23

Thanks Egyed, this solution worked. I still used the stored procedure as there are chances that more scripts will be included in the future. Please find the tested code below: `code` BEGIN DBMS_SCHEDULER.create_job (job_name => 'JOB_INSURANCE_DEACT', job_type => 'STORED_PROCEDURE', job_action => 'P_INSURANCE_DEACTIVATION', start_date => TIMESTAMP '2022-12-23 7:50:00 +04:00', enabled => TRUE); END; `code` – Rithin Rajan Dec 23 '22 at 04:23
However, I would like to add an output argument for my stored procedure, but after looking at the documentation for DBMS_SCHEDULER, I realised that this is not supported as job_action when the job or program type is STORED_PROCEDURE. So can you please help me on this as well? – Rithin Rajan Dec 23 '22 at 04:24
1

You cannot create an output parameter for a job. If you need output from a job, your best bet would be to log the information to a table while the job is running. – EJ Egyed Dec 23 '22 at 14:10

How to schedule one time executable job in Oracle?

1 Answers1