Oracle 11g - Scheduled Job Won't Run

Question

I'm trying to create a job that runs a stored procedure in Oracle. I have tried creating the job with a script, and with the UI in SQL Developer, but haven't had any luck. It shows up in the Scheduler/Jobs folder with no issues, and all the details match the script. I have changed the repeat_interval to something more frequent for testing, but that doesn't change anything. Running the job manually won't work either, though it does show a pop-up saying "Successfully processed SQL command."

I went through the list of configurations mentioned in this answer: Oracle DBMS Job not running, but everything seems correct. The Stored Procedure does run without any issues, it's just the job that's causing me problems.

Here is the script I've been using (repeat_interval's value is the desired outcome, but I've been using different values for it):

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
   job_name            =>  'J_CAPTURE_TABLE_DATA_FOR_WEEK',
   job_type            =>  'PLSQL_BLOCK', -- I've also tried 'STORED_PROCEDURE here
   job_action          =>  'CAST.SP_CAPTURE_TABLE_DATA_FOR_WEEK',
   comments            =>  'Capture Table data for older data',
   start_date          =>  SYSDATE,
   end_date            =>  null,
   repeat_interval     =>  'FREQ=WEEKLY;BYDAY=MON;BYHOUR=2;',
   enabled             =>  true,
   auto_drop           =>  false,
   number_of_arguments =>  0);
COMMIT;
END;

Desired Results: The job actually runs on the schedule (or at all).

I had the same issue - procedure/job was created/scheduled, but scheduled work wasn't started. As i remember it wasn't privileges issues. I found this article, maybe help: https://community.oracle.com/thread/648581 — Tomasz, Feb 28 '19 at 19:35
Can you show us the code you used to run the job? Don't use the GUI, use something like `begin dbms_scheduler.run_job('J_CAPTURE_TABLE_DATA_FOR_WEEK'); end; /`. Then look for the results of that run with this query: `select * from dba_scheduler_job_run_details where job_name = 'J_CAPTURE_TABLE_DATA_FOR_WEEK';` I suspect the version you have posted has a syntax error - the PL/SQL block is not valid since it doesn't have a `BEGIN` and `END`. The scheduler has a ton of features and you'll want to be familiar with the data dictionary views so you can troubleshoot them. — Jon Heller, Mar 01 '19 at 03:06
@JonHeller So, I tried using dbms_scheduler.run_job(), and it did execute the job. I probably could have elaborated, but in the code I had a comment about using "STORED_PROCEDURE" in place of "PL/SQL" in the job_type. I tried using BEGIN and END in the job_action parameter, and calling the procedure that way, but still didn't have any luck. — Dortimer, Mar 04 '19 at 15:37
@Dortimer So the job runs manually, but isn't running as scheduled? — Jon Heller, Mar 04 '19 at 23:25
Correct. Except when you right click the job in Sql Developer and hit run. I tried using the same script to create a job that runs a stored procedure against a different schema, and it worked without issues. From the information I have access to, everything seemed fine, but I'm waiting on a DBA to check their end of things. It'll probably be something dumb on my part. — Dortimer, Mar 04 '19 at 23:31

score -1 · Answer 1 · edited Oct 23 '21 at 11:05

software: oracle 11g installed locally, intellij idea

Recently, I ran into the similar stuff: created a scheduled job (with interval set) in IDEA, and the command completed successfully. But the job didn't run (it's enabled, the end date is set and everything looks good).

I googled a lot, also looked through the article mentioned by Tomasz in the comments. At last, I just ran the create job thingy from the command line, enable it, it works.

One more thing: querying the USER_SCHEDULER_JOBS table, i got the job created in the idea; querying the DBA_SCHEDULER_JOBS table, i got the job created from the command line.

Oracle 11g - Scheduled Job Won't Run

1 Answers1