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

by Jan Polnicky on 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.

No Comments Yet

Let us know what you think

See all

Share this

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

Share this

No Comments Yet

Subscribe by email