oracle - DBMS_SCHEDULER.CREATE_JOB returning "unknown command"

Question

I am trying to create a simple SQL job in Oracle using SQL Developer. This job will run daily and will execute one stored procedure.

The script is:

   DBMS_SCHEDULER.CREATE_JOB (
            job_name => '"schema1"."jobName"',
            job_type => 'STORED_PROCEDURE',
            job_action => 'schema1.import1.sp_import',
            number_of_arguments => 0,
            start_date => TO_TIMESTAMP_TZ('2021-01-12 14:31:21.000000000 AMERICA/NEW_YORK','YYYY-MM-DD HH24:MI:SS.FF TZR'),
            repeat_interval => 'FREQ=DAILY;BYTIME=144500;BYDAY=MON,TUE,WED,THU,FRI',
            end_date => NULL,
            enabled => FALSE,
            auto_drop => FALSE,
            comments => 'This job will populate date in new table.');   

Upon execution of this I am getting below error:

Error starting at line : 2 in command -
   DBMS_SCHEDULER.CREATE_JOB (
Error report -
Unknown Command

Any idea?

Answer 1

Cause of that error is, I believe, the fact that you didn't enclose DBMS_SCHEDULER.CREATE_JOB into a BEGIN-END block:

begin
  dbms_scheduler.create_job(...);
end;
/

---

Also, repeat_interval looks wrong (at least, in my 11g). There's no BYTIME - use BYHOUR and BYMINUTE combination instead. Something like this (note that I don't have your procedure):

SQL> BEGIN
  2    DBMS_SCHEDULER.CREATE_JOB (
  3      job_name        => 'test',
  4      job_type        => 'STORED_PROCEDURE',
  5      job_action      => 'p_test',
  6      number_of_arguments => 0,
  7      start_date      => TO_TIMESTAMP_TZ('2021-01-12 14:31:21.000000000 AMERICA/NEW_YORK','YYYY-MM-DD HH24:MI:SS.FF TZR'),
  8      repeat_interval => 'FREQ=DAILY;BYDAY=MON,TUE,WED,THU,FRI;BYHOUR=14;BYMINUTE=45',
  9      end_date        => NULL,
 10      enabled         => FALSE,
 11      auto_drop       => FALSE,
 12      comments        => 'This job will populate date in new table.');
 13  END;
 14  /

PL/SQL procedure successfully completed.

SQL>