oracle-databasedbms-scheduler

DBMS_SCHEDULER in apex oracle


I have an API package written. It has the GET_INFO procedure. I want this procedure to be performed in the background every 20 minutes. I understand what I should do with dbms_scheduler. But in general I do not understand where to register them . I will be grateful for the example or for your help with this)

I wrote such a code but I don't know where to use it:

BEGIN

DBMS_SCHEDULER.CREATE_JOB (

job_name = 'My_Job',

job_type = 'STORED_PROCEDURE',

job_action = 'INSERT INTO TEST2(UPDATEDAT)

VALUES (sysdate);

END;',

start_date = 'systimestamp',

repeat_interval = 'FREQ=SECONDLY;INTERVAL=5',

end_date = null,

auto_drop = FALSE,

comments = 'My new job');

END;

Here is my code and I don't know where to store it.


Solution

  • As the job type implies, you need a procedure to be created such as

    create or replace procedure Ins_Test2 is
    begin
      insert into Test2(updatedat) values(sysdate);
      commit;
    end;
    

    and then create the scheduler through

    begin
      dbms_scheduler.create_job (
         job_name           =>  'My_Job',
         job_type           =>  'STORED_PROCEDURE',
         job_action         =>  'Ins_Test2',
         start_date         =>  systimestamp, 
         repeat_interval    =>  'freq=minutely; interval = 20; byday=MON,TUE,WED,THU,FRI;',
         enabled            =>  true,
         comments           => 'My new job'
      );
    end;
    

    where I added

    byday=MON,TUE,WED,THU,FRI; as an extra direction if you want to run the scheduler within the working days(you can omit that part if you'd like).

    systimestamp(get rid of quotes) for start_date might be replaced with an upcoming time info such as start_date => '13-FEB-20 2.00.00PM Asia/Istanbul'

    in my case.

    And follow by listing the created schedulers by

    select job_name, next_run_date                           
      from dba_scheduler_jobs j;
    

    And currently running ones by

    select * 
      from user_scheduler_job_log l
     order by l.log_date desc;
    

    And drop the scheduler by

    begin
      dbms_scheduler.drop_job( job_name =>  'My_Job' );
    end;