How to implement timer for heartbeat

Question

I need to implement a heartbeat function written in PL/SQL, to ping a web service every 5 minutes. I know that PL/SQL is really not the correct language to be writing this in, but it has to be done this way.

DECLARE
stored_time   TIMESTAMP
curr_time     TIMESTAMP
BEGIN
   stored_time := current_timestamp;
   WHILE (curr_time - stored_time > 5)
      pulse_heartbeat();
      stored_time := current_timestamp;
   END WHILE

The pseudo code above is really the only way i think it could be done. I know there is a timer package with oracle, but i'm not sure if i should use it or not. Any ideas?

Do you want to execute the procedure every 5 minutes? If yes, then use `DBMS_SCHEDULER`. — Lalit Kumar B, Oct 15 '15 at 09:46
Yes, i just need the procedure to run every 5 minutes to maintain the session state — Marc Howard, Oct 15 '15 at 09:50

score 3 · Answer 1 · answered Oct 15 '15 at 09:49

3

It seems like you want to schedule the procedure execution every 5 minutes. I suggest use DBMS_SCHEDULER.

Database level

BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'pulse_heartbeat',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN pulse_heartbeat; END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'freq=minutely; interval=5; bysecond=0;',
    enabled         => TRUE);
END;
/

Above uses calendaring syntax to repeat interval every 5 minutes. It creates a job at database level.

OS level

You could also schedule scripts to run at OS level. For example, in UNIX based platforms, you could create a shell script and schedule as cron job.

answered Oct 15 '15 at 09:49

Lalit Kumar B

47,486
13
97
124

This heartbeat will need to work for individual users, rather than one job. Is it possible to create a schedule, and instantiate it multiple times for different users? – Marc Howard Oct 15 '15 at 10:11
Not clear. That functionality could be handled in your PL/SQL code. This is just a scheduling job, it will just execute the procedure at required intervals. The logic would be in the procedure. – Lalit Kumar B Oct 15 '15 at 10:15
Each user session will have it's own heartbeat. Does this mean i would have to create a scheduled job for every user? – Marc Howard Oct 15 '15 at 10:17
What does that function do? What is the exact requirement? You want if a database session is established, it should never get disconnected and should always execute a function every 5 minutes in the same session? – Lalit Kumar B Oct 15 '15 at 10:19

How to implement timer for heartbeat

1 Answers1