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

2 min read

Apr 3, 2009 12:00:00 AM

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.

On this page

Ready to unlock value from your data?

With Pythian, you can accomplish your data transformation goals and more.

Speak with Pythian consultants now →

What We Do

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

Share this

Share this

More resources

No data found -> success in dbms_scheduler

Oracle Scheduler fail to send email notifications

How to Dynamically Call PL/SQL Procedure in Oracle

Ready to unlock value from your data?