Hi All,
I was wondering anyone could help me. I have written a procedure that checks to see if a condition is true, and if it is it completes successfully. If the condition is not true it sleeps for a minute then tries again. If a timeout value is met then the procedure raises an error. This timeout is passed into the procedure in minutes.
My problem is that the procedure only works if smaller numbers are passed in. I've tested in successfully for 1,2,5,10,20 and 30 minutes, but if I pass in 90 minutes it consistently runs indefinitely (I've let it run for over 2 hours before killing it).
CREATE OR REPLACE PROCEDURE proc_wait_test (p_minutes_wait INTEGER)
IS
var_start_date DATE := SYSDATE;
var_check_result NUMBER := 0;
BEGIN
WHILE TRUE
LOOP
-- dummy check that always returns false just to test concept
SELECT count(*) INTO var_check_result FROM DUAL
WHERE 1=0;
IF (var_check_result > 0)
THEN
EXIT;
ELSE
IF (SYSDATE >= (var_start_date + (p_minutes_wait/1440))) THEN
RAISE_APPLICATION_ERROR(-20000,'Procedure has timed out, specified wait limit of '||p_minutes_wait|| ' minutes exceeded.');
END IF;
END IF;
DBMS_LOCK.SLEEP(60);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
raise;
END;
The procedure is designed to integrate into an existing solution so I can't change the basic functionality. Database version 10.2.0.2
Many thanks in advance for any assistance,
Steve.