PLSQL IMPLICIT CURSOR No Data Found After CURSOR

Question

I have a Main cursor that is working fine.

declare

    v_firm_id number;
    amount number;
    v_total_sum TABLE_TEMP.TOTAL_SUM%TYPE;

    CURSOR MT_CURSOR IS
        SELECT firm_id FROM t_firm;

BEGIN
    OPEN MT_CURSOR;
    LOOP
        FETCH MT_CURSOR INTO v_firm_id;
        EXIT WHEN MT_CURSOR%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(to_char(sysdate, 'mi:ss')  ||'--- '|| v_firm_id)
        INSERT INTO TABLE_TEMP(TOTAL_SUM) VALUES(v_firm_id) 
        COMMIT;
    END LOOP;

    DBMS_LOCK.SLEEP(20);

    BEGIN
        FOR loop_emp IN
            (SELECT TOTAL_SUM INTO v_total_sum FROM TABLE_TEMP)
        LOOP
            dbms_output.put_line(to_char(sysdate, 'mi:ss')  ||'--- '|| v_total_sum || '-TEST--');
        END LOOP loop_emp;
    END;
end;

Everything Works fine except dbms_output.put_line(v_total_sum || '---');

I do not get any data there. I get the correct number of rows. which it inserted.

Answer 1

The problem is the cursor FOR loop has a redundant into clause which it appears the compiler silently ignores, and so v_total_sum is never used.

Try this:

begin
    for r in (
        select firm_id from t_firm
    )
    loop
        insert into table_temp (total_sum) values (r.firm_id);
    end loop;

    dbms_lock.sleep(20);

    for r in (
        select total_sum from table_temp
    )
    loop
        dbms_output.put_line(r.total_sum || '---');
    end loop;

    commit;
end;

If this had been a stored procedure rather than an anonymous block and you had PL/SQL compiler warnings enabled with alter session set plsql_warnings = 'ENABLE:ALL'; (or the equivalent preference setting in your IDE) then you would have seen:

PLW-05016: INTO clause should not be specified here

I also moved the commit to the end so you only commit once.

To summarise the comments below, the Cursor FOR loop construction declares, opens, fetches and closes the cursor for you, and is potentially faster because it fetches in batches of 100 (or similar - I haven't tested in recent versions). Simpler code has less chance of bugs and is easier to maintain in the future, for example if you need to add a column to the cursor.

Note the original version had:

for loop_emp in (...)
loop
    ...
end loop loop_emp;

This is misleading because loop_emp is the name of the record, not the cursor or the loop. The compiler is ignoring the text after end loop although really it should at least warn you. If you wanted to name the loop, you would use a label like <<LOOP_EMP>> above it. (I always name my loop records r, similar to the i you often see used in numeric loops.)