CODEWITHSUNDEEP
sqloracle-databaseplsqloracle-sqldeveloper
Ron
Ron

Reputation: 19

How to set execution of operation on table on specific day in PL/SQL?

I have to do some operation on table, i.e. update on specific day of week, i.e. every Monday using only stored procedure - no triggers.

I.E. I have in the stored procedure:

update table_1 
set col1 = val1
where conditions;

I want to make working this every week on specific day.

I will be glad for any idea.

Upvotes: 1

Views: 126

Answers (1)

dcp
dcp

Reputation: 55468

I'd recommend using the Oracle job scheduler. You can set the job up to run whenever you want. You can read more about the job scheduler here, and I've included an example below.

   DBMS_SCHEDULER.CREATE_JOB (
      job_name           =>  'YOUR_JOB_NAME',
      job_type           =>  'STORED_PROCEDURE',
      job_action         =>  'SOME_SCHEMA.SOME_PROC',
      start_date         =>  to_date('20180313-23:30','YYYYMMDD-HH24:MI') AT TIME ZONE 'US/Eastern',
      repeat_interval    =>  'FREQ=DAILY;INTERVAL=1', /* every day */
      auto_drop          =>   FALSE,
      comments           =>  'Job to do something useful',
      enabled            =>  true);

Upvotes: 4

Related Questions