A more complex SQL script to monitor app status on Database Query Monitor
To monitor the status of an app, I need to monitor a value in CONPRM table called PI_HEARTBEAT, that is updated by app each second or so.
If value didn't change after a few seconds, we can assume that there's some issue in the app.
I have tried something here on database query monitor but does not seem to work, looks like too complex for use on this feature.. here's the script below which works as expected on sqlplus
any ideas?
SET serveroutput on;
DECLARE
x NUMBER;
y NUMBER;
z VARCHAR(1);
BEGIN
SELECT V into x FROM DPAADM.CONPRM where P = 'PI_HEARTBEAT';
DBMS_LOCK.sleep (15);
SELECT V into y FROM DPAADM.CONPRM where P = 'PI_HEARTBEAT';
IF (x = y) THEN
z := 'F';
DBMS_OUTPUT.put_line (z);
END IF;
END;
/
thanks!!