Scheduling a job but dba_scheduler_jobs has a null repeat interval

Question

I am creating scheduler with following statement

BEGIN
  SYS.DBMS_SCHEDULER.CREATE_SCHEDULE
    (
      schedule_name    => 'CLEAN_TABLE_EVERYDAY'
     ,start_date       => trunc(SYSDATE)
     ,repeat_interval  => 'FREQ=DAILY; BYHOUR=23;'
     ,end_date         => NULL
     ,comments         => 'Clean table at 11 PM everyday'
    );
END;
/

But when I ran following query

SELECT *
FROM dba_scheduler_jobs
where owner = 'DB_USER_NAME';

I saw that repeat_interval column has a null value.

I have left this job running for almost 3 days and I don't see any result. What is wrong here?

It does say `True`. But repeat_interval is still null ... The procedure I am calling in my program is running a procedure. The very first thing that the procedure does it inserting a log line in a local log table. Even after leaving this program running for 2-3 days, I don't see that line there — Em Ae, Nov 25 '13 at 18:42

score 0 · Answer 1 · answered Nov 25 '13 at 20:01

If you created your job with the parameter schedule_name instead of repeat_interval, like this:

begin
  DBMS_SCHEDULER.create_job (
    job_name      => 'my_job_name',
    job_type      => 'PLSQL_BLOCK',
    job_action    => 'begin null; end;',
    enabled       => TRUE,
    schedule_name => 'CLEAN_TABLE_EVERYDAY');
end;

Then in the dba_scheduler_jobs view you should look for the column schedule_name. It's normal that the repeat_interval is null.

Why is your job running for 2-3 days is another question. You must debug it. Try running the procedure through SQL+ / TOAD / SQLDeveloper first and see if there are any errors.

Scheduling a job but dba_scheduler_jobs has a null repeat interval

1 Answers1