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

Apr 2, 2009 / By Jan Polnicky

Tags: ,

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.

5 Responses to “OraQuiz #1: Quote a job name in lowercase to drop it with DBMS_SCHEDULER”

  • Yas says:

    With user DEV:

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

  • Vlado says:

    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>

  • Jan Polnicky says:

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

  • Andrey Goryunov says:

    Trying to take 3 place:

    from SYS (since not always other passwords known)

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

  • Jean-Marc Adingra says:

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

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>