Hi Harishkumar,
The issue with your procedure lies in the fact that the cursor on STATUS_TABLE is not re-fetching the updated value of the STATUS_COLUMN from the database during each iteration of the infinite loop. When a cursor is opened, it fetches a static snapshot of the data at that point in time, and subsequent changes to the table in the database are not reflected unless the cursor is closed and reopened.
Potencial solution:
CREATE OR REPLACE PROCEDURE LOOP_UNTIL_STATUS_CHANGE()
LANGUAGE SQL
BEGIN
DECLARE v_id INT;
DECLARE v_value VARCHAR(100);
DECLARE v_status CHAR(1);
DECLARE done SMALLINT DEFAULT 0;
DECLARE fl_done SMALLINT DEFAULT 1;
-- Cursor declaration for MY_TABLE
DECLARE cursor_loop CURSOR FOR
SELECT ID, VALUE_COLUMN FROM MY_TABLE;
-- Declare a handler for when the cursor reaches the end of the table
DECLARE CONTINUE HANDLER FOR NOT FOUND SET fl_done = 0;
-- Infinite loop
LOOP_INFI: LOOP
-- Open the cursor for MY_TABLE
OPEN cursor_loop;
FETCH_LOOP: LOOP
-- Fetch data from the cursor
FETCH cursor_loop INTO v_id, v_value;
IF fl_done = 0 THEN
CLOSE cursor_loop;
LEAVE FETCH_LOOP;
END IF;
SET fl_done = fl_done + 1;
CALL dbms_output.put_line('Fetch Looping ' || v_id || ' ' || v_value);
END LOOP FETCH_LOOP;
-- Dynamically fetch the latest status value
SELECT STATUS_COLUMN INTO v_status
FROM STATUS_TABLE
WHERE ID = 1
FETCH FIRST 1 ROW ONLY;
-- Exit the loop if status is 'S'
IF v_status = 'S' THEN
LEAVE LOOP_INFI;
END IF;
-- Increment the loop counter
SET done = done + 1;
CALL dbms_output.put_line('Looping ' || done || ' ' || v_status);
END LOOP LOOP_INFI;
CALL dbms_output.put_line('Out of Infinite Looping ' || done);
END@
This addresses the root cause of the issue by replacing the cursor-based approach for status_cursor with a direct query(SELECT INTO) to dynamically fetch the updated status.
Yet another solution is to reopen the Cursor Each Iteration, like this:
CREATE OR REPLACE PROCEDURE LOOP_UNTIL_STATUS_CHANGE()
LANGUAGE SQL
BEGIN
DECLARE v_id INT;
DECLARE v_value VARCHAR(100);
DECLARE v_status CHAR(1);
DECLARE done SMALLINT DEFAULT 0;
DECLARE fl_done SMALLINT DEFAULT 1;
-- Cursor declaration
DECLARE cursor_loop CURSOR FOR
SELECT ID, VALUE_COLUMN FROM MY_TABLE;
DECLARE status_cursor CURSOR FOR
SELECT STATUS_COLUMN FROM STATUS_TABLE WHERE ID = 1;
-- Declare a handler for when the cursor reaches the end of the table
DECLARE CONTINUE HANDLER FOR NOT FOUND SET fl_done = 0;
-- Infinite loop
LOOP_INFI: LOOP
-- Open the main loop cursor
OPEN cursor_loop;
FETCH_LOOP: LOOP
-- Fetch data from the main loop cursor
FETCH cursor_loop INTO v_id, v_value;
IF fl_done = 0 THEN
CLOSE cursor_loop;
LEAVE FETCH_LOOP;
END IF;
CALL dbms_output.put_line('Fetch Looping ' || v_id || ' ' || v_value);
END LOOP FETCH_LOOP;
-- Reopen the status cursor to fetch the updated status value
OPEN status_cursor;
FETCH status_cursor INTO v_status;
CLOSE status_cursor; -- Close it immediately after fetching the value
-- Debugging output
CALL dbms_output.put_line('Current Status: ' || v_status);
-- Check if the status is 'S'
IF v_status = 'S' THEN
-- Exit the infinite loop if the status is 'S'
LEAVE LOOP_INFI;
END IF;
-- Increment loop counter and print debugging output
SET done = done + 1;
CALL dbms_output.put_line('Looping ' || done || ' Status: ' || v_status);
END LOOP LOOP_INFI;
CALL dbms_output.put_line('Out of Infinite Loop after ' || done || ' iterations.');
END@
Pedro.
------------------------------
PEDRO BARBAS
------------------------------
Original Message:
Sent: Tue December 17, 2024 05:47 AM
From: Harishkumar Pathangay
Subject: inside a stored procedure loop how to break based on table column status
Hi All,
what is the problem with this procedure.
A procedure is in a infinite loop, every time it checks if status_column in table status_table is 'S', then it will break out of loop and exit procedure.
If status is other than 'S' , it will continue for ever until another application changes status_column value. the issue is it is not coming out of loop based on status value column. Please find procedure code and output also, you can see in output it reads the value 'C' and keeps on looping infinitely, but does not come out even after update of column value to 'S' .
CREATE OR REPLACE PROCEDURE LOOP_UNTIL_STATUS_CHANGE()
LANGUAGE SQL
BEGIN
DECLARE v_id INT;
DECLARE v_value VARCHAR(100);
DECLARE v_status CHAR(1);
DECLARE done SMALLINT DEFAULT 0;
DECLARE fl_done SMALLINT DEFAULT 1;
-- Cursor declaration
DECLARE cursor_loop CURSOR FOR
SELECT ID, VALUE_COLUMN FROM MY_TABLE;
DECLARE status_cursor CURSOR FOR
SELECT STATUS_COLUMN FROM STATUS_TABLE WHERE ID =1;
-- Declare a handler for when the cursor reaches the end of the table
DECLARE CONTINUE HANDLER FOR NOT FOUND SET fl_done = 0;
-- Infinite loop
LOOP_INFI: LOOP
-- Open the cursor
OPEN cursor_loop;
OPEN status_cursor;
FETCH status_cursor INTO v_status;
FETCH_LOOP: LOOP
-- Fetch data from the cursor
FETCH cursor_loop INTO v_id, v_value;
IF fl_done = 0 THEN
CLOSE cursor_loop;
LEAVE FETCH_LOOP;
END IF;
set fl_done = fl_done + 1;
call dbms_output.put_line('Fetch Looping ' || v_id || ' ' || v_value);
END LOOP FETCH_LOOP;
IF v_status = 'S' THEN
-- Exit the loop if status is 'S'
LEAVE LOOP_INFI;
END IF;
-- Close the cursor (optional in each cycle)
set done = done + 1;
set fl_done = 1;
call dbms_output.put_line('Looping ' || done || ' ' || v_status);
CLOSE status_cursor;
END LOOP LOOP_INFI;
call dbms_output.put_line('Out of Infinite Looping ' || done );
END@
the above code works infinitely, but it is not coming out of the infinite loop based on status_column value. the status column value is always C as seen at the start of procedure, it never picks up S from database after my update successful in another connection....
Fetch Looping 1 Asherah
Fetch Looping 2 Baal
Fetch Looping 3 El
Fetch Looping 4 Manat
Fetch Looping 5 Mot
Looping 21 C
Fetch Looping 1 Asherah
Fetch Looping 2 Baal
Fetch Looping 3 El
Fetch Looping 4 Manat
Fetch Looping 5 Mot
Looping 22 C
Fetch Looping 1 Asherah
Fetch Looping 2 Baal
Fetch Looping 3 El
Fetch Looping 4 Manat
Fetch Looping 5 Mot
Looping 23 C
As you can see in output , it never picks up S at all keeps going in infinite loop.
how to solve it?
Thanks
Harishkumar
------------------------------
Harishkumar Pathangay
------------------------------