ORA-20001 when dropping DBMS_SCHEDULER job with white spaces in name

Environment: Oracle Database 11g EE 11.2.0.3.0

When I tried to drop a scheduled job using DBMS_SCHEDULER.DROP_JOB, I received the following error message:

BEGIN
DBMS_SCHEDULER.DROP_JOB(
job_name => 'MATTHIASH.gather schema stats SCOTT'
);
END;

ORA-20001: comma-separated list invalid near T
ORA-06512: in "SYS.DBMS_UTILITY", regel 236
ORA-06512: in "SYS.DBMS_UTILITY", regel 272
ORA-06512: in "SYS.DBMS_SCHEDULER", regel 623
ORA-06512: in line 2

I was puzzled for a while… and then I saw what the problem was: the job name (“gather schema stats SCOTT”) contains white spaces, and this causes DBMS_SCHEDULER.DROP_JOB to throw an error…
However, there’s a workaround! If you enclose the job name in double quotes, the procedure DROP_JOB works fine:

BEGIN
DBMS_SCHEDULER.DROP_JOB(
job_name => 'MATTHIASH."gather schema stats SCOTT"'
);
END;

Btw: the same issue occurs with other DBMS_SCHEDULER procedures, like CREATE_JOB, RUN_JOB and such…

HTH,
Matthias

4 Responses to ORA-20001 when dropping DBMS_SCHEDULER job with white spaces in name

  1. dbms_scheduler jobs, unlike dbms_job, are oracle objects (you can see them in user_objects)

    Hence they follow all the normal rules for object names. Just like if you had a table name with mixed case or with spaces in it.

  2. puni says:

    try this
    begin
    DBMS_SCHEDULER.DROP_JOB
    (
    job_name => ‘”DOORCOUNTS”.”Process Photos Job”‘
    );
    end;
    /

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: