Mick Greener Mick Greener - 3 months ago 8
SQL Question

Why does my explicit cursor fetch only specific rows from my database in PL/SQL?

I am very new to PL/SQL and I am trying to use an explicit cursor to iterate over my database, FLEX_PANEL_INSPECTIONS. I would like to fetch each row from the database in turn using an explicit cursor, and depending on the randomly generated 'status' of a given 'panel' in the row, assign the panel a new status value within an if / else statement. The status of the panel is random but Boolean - it is either 1 or 0.
However, when I view the DBMS output, I note that the fetch does not retrieve all values from the database - only those who have a status value of 1. I have included the core code below.
I would be very grateful if anybody is able to help me find a solution, or explain the root cause of my problem, thanks!

create or replace procedure FLEX_SUMMARY_STATUS_PROCEDURE as

old_panel_status number;
new_panel_status number;

cursor panel_cursor is
select FLEX_PANEL_STATUS
from FLEX_PANEL_INSPECTIONS;

begin

open panel_cursor;
loop

fetch panel_cursor into old_panel_status;
exit when panel_cursor%notfound;

if old_panel_status = 0
then new_panel_status := 2;
elsif old_panel_status = 1
then new_panel_status := 3;
--More conditional loops follow (but are irrelevant for this question).

dbms_output.put_line(old_panel_status);
--Test output
--This displays all of the 1's that were randomly generated in the original table.
--It does not display any of the 0's that were generated.

end if;
end loop;

close panel_cursor;
close sensor_cursor;

end FLEX_SUMMARY_STATUS_PROCEDURE;
/

Answer

If you didn't make a mistake when removing the additional elseif clauses, the issue is in the location of your dbms_output.put_line.

It's located inside the else part, so will only trigger when this clause is called. Move it below the END IF and make sure to use proper indentation, which makes such things way easier to spot.

create or replace procedure FLEX_SUMMARY_STATUS_PROCEDURE as

old_panel_status number;
new_panel_status number;

cursor panel_cursor is
    select FLEX_PANEL_STATUS
    from FLEX_PANEL_INSPECTIONS;

begin

    open panel_cursor;
    loop

        fetch panel_cursor into old_panel_status;
        exit when panel_cursor%notfound;

        if old_panel_status = 0
            then new_panel_status := 2;
        elsif old_panel_status = 1
            then new_panel_status := 3;
        --More conditional loops follow (but are irrelevant for this question)
        end if;

    dbms_output.put_line(old_panel_status);

    end loop;

    close panel_cursor;
    close sensor_cursor;

end FLEX_SUMMARY_STATUS_PROCEDURE;
/