PL/SQL Error: A loop that contains DML statements should be refactored to use BULK COLLECT and FORALL
<p>I've searched across the whole internet for some examples, but I still can't get my head around why I can not use DML statement inside this cursor. I'm kind of missing the theory behind it, but I won't deny an example of how to do write this correctly would make my life lots easier as well. Here is the query I'm working on (Note: I removed exits when not found results, close if cursor already open and things like that just to focus on the main point here):</p>

<pre><code>DECLARE
    // lots of vars

    // the cursor below gets all datasources connected to Node XXYZ123
    CURSOR DataSourceCheck
    IS
        SELECT NODENAAM, NAAM, URL, DBNODE1, DBNODE2, DBUSERNAAM, DBNAAM
        FROM SCHEMA.TABLENAME
        WHERE NODENAAM = 'XXYZ123';

    // this cursor will execute row-by-row based on the result set of above cursor
    CURSOR CheckIfOnlyDataSource
    IS
        SELECT NODENAAM, NAAM, URL, DBNODE1, DBNODE2, DBUSERNAAM, DBNAAM
        FROM SCHEMA.TABLENAME
        WHERE DBUSERNAAM = var_dbusernaam AND (DBNode1 = var_dbnode1 OR DBNode2 = var_dbnode2);

BEGIN  
    OPEN DataSourceCheck;   
    LOOP
        FETCH DataSourceCheck into var_nodenaam, var_naam, var_URL, var_dbnode1, var_dbnode2, var_dbusernaam, var_dbnaam;

        var_rowcount:= 0;
        OPEN CheckIfOnlyDataSource;     
            LOOP
                FETCH CheckIfOnlyDataSource into var_nodenaam2, var_naam2, var_URL2, var_dbnode12, var_dbnode22, var_dbusernaam2, var_dbnaam2;
                var_rowcount:= var_rowcount + 1;
            END LOOP;  

            // only save result in a temp table when var_rowcount is 1 and not higher.
            IF var_rowcount = 1
                THEN
                    INSERT INTO global_temp_table
                    (t_dbusernaam, t_nodenaam, t_dbnode1, t_dbnode2, t_distinctcount)
                    VALUES
                    (var_dbusernaam2, var_nodenaam2, var_dbnode12, var_dbnode22, var_rowcount)
            END IF;
        CLOSE CheckIfOnlyDataSource;
    END LOOP;    
END;
</code></pre>

<p>The point of failure is this part, with the message that DML should be reconfigured into FORALL or BULK INTO statements:</p>

<pre><code>IF var_rowcount = 1
    THEN
        INSERT INTO global_temp_table
        (t_dbusernaam, t_nodenaam, t_dbnode1, t_dbnode2, t_distinctcount)
        VALUES
        (var_dbusernaam2, var_nodenaam2, var_dbnode12, var_dbnode22, var_rowcount)
END IF;
</code></pre>

<p>I don't understand why DML is not working in a row-by-row approach? The output is clearly stored inside the variables <code>var_dbusernaam2</code>, <code>var_nodenaam2</code>, <code>var_dbnode12</code> and <code>var_dbnode22</code>, hence I can do a <code>dbms_output.put_line</code> to show them. But if it is stored into the variable already, then why can't I just store it simply into a table (this isn't billions of bulk data, not even 1000 records!).</p>

<p>Is there no simple workaround? I gave BULK COLLECT and FORALL a try, but I need a lot more time to invest to understand it and get the query right - the cursor in a cursor definately won't make it any easier.</p>