DBMS_SCHEDULER in apex oracle

Question

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.

Its a stored procedure. Oracle apex is just the frontend of the oracle database. So everything is stored in the database. What you actually do with this is run it. You dont save the code somewhere, you run the code and it creates the job. If you are only using APEX, and dont have sql developer in the backend. Then you could go to SQL WORKSHOP and then SQL COMMANDS in APEX. And run the code there. I have never done it like this, as SQL Developer really is necessary for any good functionality, but it should work. — TineO, Feb 13 '20 at 09:37
@TineO I wanted to do it right away, but I got the error, "PLS-00201: identifier 'JOB_NAME' must be declared", although I know that declared should not be in this code! — Apex_MAN, Feb 13 '20 at 09:41
@TineO I also tried the same on developers, the same mistake! — Apex_MAN, Feb 13 '20 at 09:48
@TineO thanks, I have one more question, I'm in the package, (API) wrote the procedure (GET_INFO), I want to use it here as a task. I understand correctly that I have to specify this as job_action => 'API.GET_INFO'? — Apex_MAN, Feb 13 '20 at 10:10
In the job action you include the action you want performed in ' '. So if that action is 'INSERT INTO ...' thats fine, it can also be a procedure call 'API.GET_INFO'. So yes, provided GET_INFO is a procedure in the package called API, you can call it like that. — TineO, Feb 13 '20 at 10:35

Barbaros Özhan · Accepted Answer · 2020-02-13T10:33:14.823

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;

DBMS_SCHEDULER in apex oracle

1 Answers1