2

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.

Oxax
  • 91
  • 8

1 Answers1

1

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.

kevinskio
  • 4,431
  • 1
  • 22
  • 36
  • Thanks for the reply... but doing this will mean the sms get sent every hour if there is something in the table. i guess i should add a check if it the sms has been sent before. i.e if table has content and sms has not been then sent else do nothing – Oxax Apr 24 '15 at 10:20
  • @Oxax sounds like you have answered your own question – kevinskio Apr 24 '15 at 11:01