To terminate an intentionally infinite loop in Oracle, we can use DBMS_PIPE.
First, create a pipe.
DBMS_PIPE.create_pipe(pipename);
Then, insert message handling into loop block.
if DBMS_PIPE.receive_message(pipename,0) = 0 then
DBMS_PIPE.unpack_message(pipebuf);
Exit when pipebuf = 'stop';
end if;
When needed, send a message which content is "stop" to the pipe.
DBMS_PIPE.create_pipe(pipename);
DBMS_PIPE.pack_message('stop');
So the whole example is:
DECLARE
pipename CONSTANT VARCHAR2(12) := 'signaler';
result INTEGER;
pipebuf VARCHAR2(64);
BEGIN
result := DBMS_PIPE.create_pipe(pipename);
LOOP
xxxxx
DBMS_LOCK.sleep(10);
IF DBMS_PIPE.receive_message(pipename,0) = 0
THEN
DBMS_PIPE.unpack_message(pipebuf);
EXIT WHEN pipebuf = 'stop';
END IF;
END LOOP;
END;
DECLARE
pipename VARCHAR2(12) := 'signaler';
result INTEGER := DBMS_PIPE.create_pipe(pipename);
BEGIN
DBMS_PIPE.pack_message('stop');
END;