OraQuiz #1: Quote a job name in lowercase to drop it with DBMS_SCHEDULER

Posted in: Technical Track

I have enjoyed reading Sheeri’s nice MySQL “Pop Quiz” series, and, knowing that practise greatly increases the retention time of information in our minds, I thought I would start a series of quizzes of my own, only to do with Oracle.

Here’s a an easy one to begin with. How do you drop a job with job_name in lower case using DBMS_SCHEDULER on Oracle 10g?

SQL> SELECT owner,'.'||job_name||'.' job_name
FROM all_scheduler_jobs;

OWNER   JOB_NAME
------- ----------
DEV     .job1.

1 rows selected.

Note: The syntax '.'||job_name||'.' is used only to confirm that there are no spaces in the job name.

Now, just a few hints where not to go:

SQL> show user
USER is "DEV"

SQL> exec DBMS_SCHEDULER.DROP_JOB( job_name => 'job1', force => TRUE );
BEGIN DBMS_SCHEDULER.DROP_JOB( job_name => 'job1', force => TRUE ); END;

*
ERROR at line 1:
ORA-27475: "DEV.JOB1" must be a job
ORA-06512: at "SYS.DBMS_ISCHED", line 178
ORA-06512: at "SYS.DBMS_SCHEDULER", line 544
ORA-06512: at line 1

SQL> exec DBMS_SCHEDULER.DROP_JOB( job_name => 'DEV.job1', force => TRUE );
BEGIN DBMS_SCHEDULER.DROP_JOB( job_name => 'DEV.job1', force => TRUE ); END;

*
ERROR at line 1:
ORA-27475: "DEV.JOB1" must be a job
ORA-06512: at "SYS.DBMS_ISCHED", line 178
ORA-06512: at "SYS.DBMS_SCHEDULER", line 544
ORA-06512: at line 1

SQL> exec DBMS_SCHEDULER.DROP_JOB( job_name => "job1", force => TRUE );
BEGIN DBMS_SCHEDULER.DROP_JOB( job_name => "job1", force => TRUE ); END;

*
ERROR at line 1:
ORA-06550: line 1, column 44:
PLS-00201: identifier 'job1' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

SQL> show user
USER is "SYS"

SQL> exec dbms_scheduler.drop_job('DEV.job1', force => TRUE);
BEGIN dbms_scheduler.drop_job('DEV.job1'); END;

*
ERROR at line 1:
ORA-27475: "DEV.JOB1" must be a job
ORA-06512: at "SYS.DBMS_ISCHED", line 178
ORA-06512: at "SYS.DBMS_SCHEDULER", line 544
ORA-06512: at line 1

SQL> exec DBMS_SCHEDULER.DROP_JOB( job_name => '"job1"', force => TRUE );
BEGIN DBMS_SCHEDULER.DROP_JOB( job_name => '"job1"', force => TRUE ); END;

*
ERROR at line 1:
ORA-27475: "SYS.job1" must be a job
ORA-06512: at "SYS.DBMS_ISCHED", line 178
ORA-06512: at "SYS.DBMS_SCHEDULER", line 544
ORA-06512: at line 1

Since I have provided you with all the results of blind tests, I guess it won’t take you too much time! So who will be first to answer OraQuiz #1? If we don’t get an answer by Monday, I’ll post it.

email

Interested in working with Jan? Schedule a tech call.

About the Author

http://www.linkedin.com/in/polnicky

5 Comments. Leave new

With user DEV:

exec DBMS_SCHEDULER.DROP_JOB( job_name => ‘”job1″‘, force => TRUE );

Reply

SQL>exec DBMS_SCHEDULER.CREATE_JOB (job_name=>'”job1″‘,job_type => ‘PLSQL_BLOCK’,job_action => ‘begin null; end;’);

PL/SQL procedure successfully completed.

SQL>select job_name from dba_scheduler_jobs where job_name =’job1′;

JOB_NAME
——————————
job1

SQL>exec DBMS_SCHEDULER.drop_JOB (job_name=>'”job1″‘, force=>TRUE);

PL/SQL procedure successfully completed.

SQL>

Reply
Jan Polnicky
April 6, 2009 4:22 pm

Well done Yas – You got the very first point from OraQuiz. Vlado got just a half :) Will publish a next Quiz soon!

Reply
Andrey Goryunov
April 7, 2009 8:54 am

Trying to take 3 place:

from SYS (since not always other passwords known)

exec DBMS_SCHEDULER.drop_job(‘DEV1.”job1″‘);

Reply
Jean-Marc Adingra
August 17, 2012 3:54 am

It works, thanks for the command.
The tip was to put the job name between
‘ ” ” ‘

Reply

Leave a Reply

Your email address will not be published. Required fields are marked *