Showing posts with label remove job. Show all posts
Showing posts with label remove job. Show all posts

Friday, July 5, 2013

Remove job from Oracle

There are few types of jobs:
till Orecle10 - packages DBMS_JOB is used.
from Oracle11 - package DBMS_SCHEDULER is used.

DBMS_JOB are used numbers for identifier.
DBMS_SCHEDULER use valid oracle object name (that can not be started from numbers) 
Links to spec: dbms_job is deprecatedschedulerdbms_joball_jobs.

when you try to remove old job by new package you got (2364 is job identifier):

Error starting at line 1 in command:
BEGIN
  DBMS_SCHEDULER.DROP_JOB('2364');
END;
Error report:
ORA-20001: comma-separated list invalid near ,
ORA-06512: at "SYS.DBMS_UTILITY", line 236
ORA-06512: at "SYS.DBMS_UTILITY", line 272
ORA-06512: at "SYS.DBMS_SCHEDULER", line 623
ORA-06512: at line 2 



From SQLDeveloper I got different error when use old package DBMS_JOB:
DBMS_JOB.remove(2364);

[ERROR] ORA-23421: job number 2364 is not a job in the job queue
[ERROR] ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
[ERROR] ORA-06512: at "SYS.DBMS_IJOB", line 770
[ERROR] ORA-06512: at "SYS.DBMS_JOB", line 180
[ERROR] ORA-06512: at line 2

But it was ok from sqlplus under owner user :

BEGIN DBMS_JOB.remove(2364); END; commit;