Reshcedule oracle DBMS_SCHEDULER job based on condition

2
votes

I have a daily scheduled job that runs a procedure that sends sms at a particular time daily. But there is not guarantee that the source table for the sms will be populated by that time , i want to be able to reschedule the job for another hour if the source table is not yet populated. how do i go about this.

1
oracleoracle11gdbms-scheduler

1 Answers

1
votes

Just schedule the job for every hour and put a condition in the procedure so that it does not do anything if there is nothing to do.

--in pseudo code
CREATE OR REPLACE
Your_procedure is
v_count NUMBER(9);
BEGIN
select count(*)
INTO v_count
FROM sms_table;

IF v_count > 0 THEN
  --do something
ELSE
  --log the count into a logging table
END IF;

END;

For a more sophisticated approach this post from Martin Fowler is thought provoking.