CODEWITHSUNDEEP
oracle-databasestored-proceduresplsqldbms-scheduler
Manoj
Manoj

Reputation: 5612

dbms_scheduler.run_job('jobName) fails to run

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.

Upvotes: 12

Views: 119407

Answers (2)

Ramiro Juarez
Ramiro Juarez

Reputation: 235

you're running manually the JOB as:

dbms_scheduler.run_job('UPDATE_PLAYER_STATES')

Oracle think this is a command, but is not, that's why you receive error message, "Unknown command".

The correct way to execute it is:

BEGIN
   dbms_scheduler.run_job('UPDATE_PLAYER_STATES');
END;

Once you achieved to execute it, if your schema do not have enough privileges, then you will see something like "Object do not exist or you don't have permissions", which is probably the root cause of the issue.

Best Regards :)

Upvotes: 12

Dave Costa
Dave Costa

Reputation: 48121

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?

Upvotes: 20

Related Questions