Re: Schroedingers Job - a job exists but doesn't at the same time.

From: Mohamed Houri <mohamed.houri_at_gmail.com>
Date: Mon, 16 Dec 2013 14:18:49 +0100
Message-ID: <CAJu8R6gm_H21eKcRa0h4OzjMpMSPvbXjSVbywkjHtzKOoP+-Vw_at_mail.gmail.com>

It seems that the parameter repeat_interval has its importance here

SQL> BEGIN
  3 DBMS_SCHEDULER.create_job (

  4      job_name        => 'MY_TEST_JOB_1',
  5      job_type        => 'PLSQL_BLOCK',
  6      job_action      => 'BEGIN DBMS_STATS.gather_schema_stats(''T1'');
END;',
  7      start_date      => SYSTIMESTAMP,
  8      repeat_interval => 'freq=hourly; byminute=0',
  9      end_date        => NULL,
 10      enabled         => TRUE
 11      );

 12 END;
 13 /

PL/SQL procedure successfully completed.

SQL> SELECT job_name FROM user_scheduler_jobs where job_name like '%MY%';

JOB_NAME

---

MY_TEST_JOB_1 SQL> BEGIN
  2 -- Job defined entirely by the CREATE JOB procedure.   3 DBMS_SCHEDULER.create_job (

  4      job_name        => 'MY_TEST_JOB_2',
  5      job_type        => 'PLSQL_BLOCK',
  6      job_action      => 'BEGIN DBMS_STATS.gather_schema_stats(''T1'');
END;',
  7      start_date      => SYSTIMESTAMP,
  8    --  repeat_interval => 'freq=hourly; byminute=0',
  9      end_date        => NULL,
 10      enabled         => TRUE
 11      );

 12 END;
 13 /

PL/SQL procedure successfully completed.

SQL> SELECT job_name FROM user_scheduler_jobs where job_name like '%MY%';

JOB_NAME

---

MY_TEST_JOB_1 SQL> BEGIN
  2 -- Job defined entirely by the CREATE JOB procedure.   3 DBMS_SCHEDULER.create_job (

  4      job_name        => 'MY_TEST_JOB_3',
  5      job_type        => 'PLSQL_BLOCK',
  6      job_action      => 'BEGIN DBMS_STATS.gather_schema_stats(''T1'');
END;',
  7    --  start_date      => SYSTIMESTAMP,
  8      repeat_interval => 'freq=hourly; byminute=0',
  9      end_date        => NULL,
 10      enabled         => TRUE
 11      );

 12 END;
 13 /

PL/SQL procedure successfully completed.

SQL> SELECT job_name FROM user_scheduler_jobs where job_name like '%MY%';

JOB_NAME

---

MY_TEST_JOB_1
MY_TEST_JOB_3 SQL> BEGIN
  2 DBMS_SCHEDULER.drop_job (job_name => 'MY_TEST_JOB_2');   3 END;
  4 /
BEGIN
*
ERROR at line 1:

ORA-27475: ".MY_TEST_JOB_2" must be a job
ORA-06512: at "SYS.DBMS_ISCHED", line 213
ORA-06512: at "SYS.DBMS_SCHEDULER", line 657
ORA-06512: at line 2

SQL> BEGIN
  2 -- Job defined entirely by the CREATE JOB procedure.   3 DBMS_SCHEDULER.create_job (

  4      job_name        => 'MY_TEST_JOB_2',
  5      job_type        => 'PLSQL_BLOCK',
  6      job_action      => 'BEGIN DBMS_STATS.gather_schema_stats(''T1'');
END;',
  7      start_date      => SYSTIMESTAMP,
  8      repeat_interval => 'freq=hourly; byminute=0',
  9      end_date        => NULL,
 10      enabled         => TRUE
 11      );

 12 END;
 13 /

PL/SQL procedure successfully completed.

SQL> SELECT job_name FROM user_scheduler_jobs where job_name like '%MY%';

JOB_NAME

---

MY_TEST_JOB_1
MY_TEST_JOB_2
MY_TEST_JOB_3

Mohamed Houri

2013/12/16 Thomas Kellerer <thomas.kellerer_at_mgm-tp.com>

> Tim Gorman, 16.12.2013 13:43:
> > I'm struck by your usage of the UPPER function in all your queries.
> What happens if you re-run those queries without the use of UPPER?
>
> I used that to make sure that I don't miss anything that _might_ have been
> create in lowercase (or mixed case).
>
> As I'm comparing that value with an all uppercase value, it should return
> any object regardless whether it was created in lower, upper or mixed-case.
>
> Regards
> Thomas
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
Bien Respectueusement
Mohamed Houri

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Dec 16 2013 - 14:18:49 CET