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
------------------------------