dbms_scheduler.run_job('jobName) fails to run

oracle stored-procedures plsql dbms-scheduler
Manoj picture Manoj · Aug 25, 2011 · Viewed 94.6k times · Source

I'm trying to run a scheduled job manually, the job looksa like this

  DBMS_SCHEDULER.CREATE_JOB (
   job_name           =>  'UPDATE_PLAYER_STATES',
   job_type           =>  'STORED_PROCEDURE',
   job_action         =>  'PLAYER_STATE_UPDATER',
   repeat_interval    =>  'FREQ=DAILY;BYHOUR=0', /* every day at Midnight */
   job_class          =>  'DEFAULT_JOB_CLASS',
   enabled            =>  true,
   auto_drop          =>  false);

Now when I run the procedure using execute PLAYER_STATE_UPDATER I can see the desired result , but the job is failing to execute as shown by

select log_date, job_name, status, run_duration
from dba_scheduler_job_run_details where job_name='UPDATE_PLAYER_STATES' or status='FAILED';


    LOG_DATE                    JOB_NAME        STATUS   RUN_DURATION 
------------- -----------------------------------------------------------------
23-AUG-11 00.20.24.288887000 +01:00 UPDATE_PLAYER_STATES    FAILED    0 0:0:0.0    
22-AUG-11 10.27.24.537659000 +01:00 UPDATE_PLAYER_STATES    FAILED    0 0:0:0.0    
22-AUG-11 10.28.50.447042000 +01:00 UPDATE_PLAYER_STATES    FAILED    0 0:0:0.0    
22-AUG-11 10.30.30.018891000 +01:00 UPDATE_PLAYER_STATES    FAILED    0 0:0:0.0    
25-AUG-11 10.59.02.332579000 +01:00 UPDATE_PLAYER_STATES    FAILED    0 0:0:0.0    
25-AUG-11 10.59.15.980730000 +01:00 UPDATE_PLAYER_STATES    FAILED    0 0:0:0.0    
25-AUG-11 10.59.27.823131000 +01:00 UPDATE_PLAYER_STATES    FAILED    0 0:0:0.0    
25-AUG-11 11.01.04.798364000 +01:00 UPDATE_PLAYER_STATES    FAILED    0 0:0:0.0    
24-AUG-11 00.20.24.419251000 +01:00 UPDATE_PLAYER_STATES    FAILED    0 0:0:0.0    
25-AUG-11 00.20.24.299180000 +01:00 UPDATE_PLAYER_STATES    FAILED    0 0:0:0.0    
25-AUG-11 09.35.24.798535000 +01:00 UPDATE_PLAYER_STATES    FAILED    0 0:0:0.0

And executing the job manually is failing too dbms_scheduler.run_job('UPDATE_PLAYER_STATES'); with the error

Error starting at line 1 in command:
dbms_scheduler.run_job('UPDATE_PLAYER_STATES')
Error report:
Unknown Command

What am I missing.

Answer

Dave Costa picture Dave Costa · Aug 25, 2011

When you're trying to run the job manually, it looks like you are simply not using the correct syntax in SQL Developer. You need to use execute dbms_scheduler.run_job('UPDATE_PLAYER_STATES'). Of course that doesn't explain why the job is failing.

My guess is there's something wrong with the job configuration such that it can't even start; but I don't see what it is. You might try including the schema name in the job_action to make sure it is not looking in the wrong schema.

Is there anything of interest in the other columns of dba_scheduler_job_run_details -- particularly error# or additional_info?

Related questions

Oracle Error ORA-06512

Just can't figure out why it gives me ORA-06512 Error PROCEDURE PX(pNum INT,pIdM INT,pCv VARCHAR2,pSup FLOAT) AS vSOME_EX EXCEPTION; BEGIN IF ((pNum < 12) OR (pNum > 14)) THEN RAISE vSOME_EX; ELSE EXECUTE IMMEDIATE 'INSERT INTO …

Read More
Passing an array of data as an input parameter to an Oracle procedure

I'm trying to pass an array of (varchar) data into an Oracle procedure. The Oracle procedure would be either called from SQL*Plus or from another PL/SQL procedure like so: BEGIN pr_perform_task('1','2','3…

Read More
Truncating a table in a stored procedure

When I run the following in an Oracle shell it works fine truncate table table_name But when I try to put it in a stored procedure CREATE OR REPLACE PROCEDURE test IS BEGIN truncate table table_name; END test; / …

Read More