Is there a way to make an Oracle Job disable itself?

Question

I'm trying to make this happen:

declare
  Contador number;

begin
 ATUALIZAR_VAL_MAT_PENDENTES(Contador);

 if Contador = 0 then
  dbms_scheduler.disable('JOB_ATUALIZAR_VAL_MAT_PEND');
  end if;
end;

When the counter returns zero, it will disable this very JOB. But, I'm getting:

"ORA-27478: o job "SPEDO.JOB_ATUALIZAR_VAL_MAT_PEND" está em execução"

This last bit means "it's executing".

So, I'm thinking this is because the job is running and it can't shut down itself.

I think that another approach could be to alter the end date, but I can't seem to find the syntax to do this.

Can anyone help? Is this achievable?

What is the business problem that you are trying to solve that would lead you to want a job to disable itself? Technically, I suppose you could submit a new job that disables the existing job once it finishes. But I'm hard-pressed to imagine a situation where I'd want that behavior rather than, say, the job failing and not being re-executed or creating a job that only runs a fixed number of times or a job that only runs when there is some work to do. — Justin Cave, Jan 06 '15 at 17:56
I need to run a procedure that checks weather the users have paid their debts or not and if it's still within the due date. When it's expired and not paid, I will recalculate all the values again until there's no debts with the old value. This happens only once every year, so, I don't need to be checking it forever. @Justin Cave — Vic Wolk, Jan 06 '15 at 18:36
I'm not sure that I understand. It sounds like you are saying that you're trying to disable the job rather than defining the proper schedule for the job. If you want the process to check something every day for some number of days at the beginning of the year and then do nothing for the rest of the year, define a schedule to that effect. — Justin Cave, Jan 06 '15 at 18:40
And how do I know, before hand, when it should stop? Imagine if you need to change the value of a certain service from $100 to $101. But, there are several clients that have their bills with the due date not yet expired, so, the client is still able to pay the old price. Plus, you have no control on how many days it could be until the bill expires, for that information belongs to and is generated by another system. Only when there are no more bills to have their prices revised, the job should stop. @Justin Cave — Vic Wolk, Jan 06 '15 at 19:02
If that's the case, I'd just let the job run daily and accept that it will have very little to do by the end of the year (and thus it should take virtually no time at the end of the year). If some customer's expiration date could be December 30, it makes little sense to disable the job for one day. — Justin Cave, Jan 06 '15 at 19:05
you should have a pre caution at the very begining of your job. If "Contador" is "0" then finish. — PT_STAR, Jan 09 '15 at 09:11

score 3 · Accepted Answer · answered Jan 06 '15 at 20:16

Thanks to @Shankar for the help. This is how I managed to accomplish what I was trying to do:

declare
  Contador number;

begin
 ATUALIZAR_VAL_MAT_PENDENTES(Contador);

 if Contador = 0 then
   dbms_scheduler.set_attribute('JOB_ATUALIZAR_VAL_MAT_PEND', 'end_date', systimestamp + 1);
  end if;
end;

I had already thought of this, but when the intelisense poped up saying that the "value" parameter was a "boolean", I didn't even try to pass a date to it. Now it's working fine, but there's one think I must add:

This doesn't work if you attempt to set the end date to an hour or minutes ahead in time. You need to actually change the day or it will give you the ORA-27483: "string.string" has an invalid END_DATE.

Thanks for the hint. Actually it is enough for `end_date` to be just a second ahead in the future: `dbms_scheduler.set_attribute(jobName, 'end_date', systimestamp+1/24/60/60);` — Vadzim, May 12 '20 at 01:15

score 0 · Answer 2 · answered Jan 06 '15 at 18:04

0

I guess you could do two things,

Force stop the job and then disable it.

declare
  Contador number;    
begin
 ATUALIZAR_VAL_MAT_PENDENTES(Contador);

 if Contador = 0 then
  DBMS_SCHEDULER.stop_JOB (job_name => 'JOB_ATUALIZAR_VAL_MAT_PEND',force=>true);
  dbms_scheduler.disable('JOB_ATUALIZAR_VAL_MAT_PEND');
  end if;
end;

Check for job completion and then disable.

SELECT status FROM USER_SCHEDULER_JOB_LOG
WHERE job_name = 'JOB_ATUALIZAR_VAL_MAT_PEND' and operation = 'RUN' ORDER BY log_date desc
offset 0 rows fetch next 1 row only; -- This will give you the latest run's status

store this into a variable ( say status_) and then check like the below,

declare
  Contador number;

begin
 ATUALIZAR_VAL_MAT_PENDENTES(Contador);

 if Contador = 0 and status_ ="Succeeded" then
  DBMS_SCHEDULER.stop_JOB (job_name => 'JOB_ATUALIZAR_VAL_MAT_PEND',force=>true);
  dbms_scheduler.disable('JOB_ATUALIZAR_VAL_MAT_PEND');
  end if;
end;

answered Jan 06 '15 at 18:04

Shankar

879
8
15

But, I'm attempting this inside the very job I'm trying to disable. Don't you think forcing a stop would bring it to ignore the second command? Also, the "contador" variable is already to define the success of the operation. It means it doesn't have anything else to process and it should stop running @Shankar – Vic Wolk Jan 06 '15 at 18:24
oh sorry, completely missed that in your question. A job can disable itself if, - if it has an end_date set which has passed - if it points to a program which has been dropped - if it has max_runs or max_failures set and either has been reached – Shankar Jan 06 '15 at 18:59
And how can I set the end_date on the fly? I mean, when that "contador" is zero, I would like to change the end_date to sysdate+1. Is there a way to do this? @Shankar – Vic Wolk Jan 06 '15 at 19:04
1

did u try this, begin dbms_scheduler.set_attribute ( name => 'your job name', attribute => 'end_date', value => '01-mar-2015 07:00:00 am'); end; The end_date is a timestamp field, fyi... – Shankar Jan 06 '15 at 19:09

Is there a way to make an Oracle Job disable itself?

2 Answers2