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

From: Howard Latham <howard.latham_at_gmail.com>
Date: Mon, 16 Dec 2013 11:38:35 +0000
Message-ID: <CAPCNhx3GstvRH-yX_pgvp200LEZ_uMEQkOKM4kk1hzxu26PAvQ_at_mail.gmail.com>



Surely if its schroedingers job then if you don't look it will exist.

On 16 December 2013 11:10, Thomas Kellerer <thomas.kellerer_at_mgm-tp.com>wrote:

> Hello list,
>
> I have a strange problem when trying to create a job using the
> DBMS_SCHEDULER package:
>
> I verified that the job name does not exists using the following
> statements:
> (All the following statements have been run using the same Oracle user:
> MY_USER)
>
>
> select count(*)
> from all_scheduler_jobs
> where upper(job_name) = 'MY_JOBNAME';
> ==> returns 0
>
> select count(*)
> from all_scheduler_running_jobs
> where upper(job_name) = 'MY_JOBNAME';
> ==> returns 0
>
> select count(*)
> from all_jobs
> where upper(what) like '%MY_JOBNAME%'
> ==> returns 0
>
> select count(*)
> from all_scheduler_chains
> where upper(chain_name) = 'MY_JOBNAME';
> ==> returns 0
>
> select count(*)
> from all_scheduler_groups
> where upper(group_name) = 'MY_JOBNAME';
> ==> returns 0
>
> select count(*)
> from all_objects
> where upper(object_name) = 'MY_JOBNAME';
> ==> returns 0
>
>
> execute DBMS_SCHEDULER.drop_job(job_name => 'MY_JOBNAME', force => true);
> ==> ORA-27475: "MY_USER.MY_JOBNAME" must be a job
>
>
> But when I try to create a job with that name using:
>
> execute DBMS_SCHEDULER.create_job(job_name => 'MY_JOBNAME', job_type =>
> 'PLSQL_BLOCK', job_action => 'begin ... end;', enabled => TRUE);
>
> I get the following error: ORA-27477: "MY_USER.MY_JOBNAME" already exists
>
> If I use a different job name (e.g. MY_JOBNAMEX) I can create and drop the
> job without problems.
>
> Does anyone have an idea on where else I could look for an object with
> that name that prevents creating the job?
>
> I am using Oracle 11.2.0.3 on CentOS
>
>
> Thanks in advance
> Thomas
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
Howard A. Latham

--
http://www.freelists.org/webpage/oracle-l
Received on Mon Dec 16 2013 - 12:38:35 CET

Original text of this message