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

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.