if-statementplsqlbulkinsertbulk-loadforall

IF ELSE Condition inside FORALL Oracle


Can you guys share with how to do IF ELSE Inside a FORALL?

This is my current code that working fine.

FOR DECLARATION;

TYPE t_column1 IS TABLE OF USERS.column1%TYPE;
TYPE t_column2 IS TABLE OF USERS.column1%TYPE;

arr_column1     t_column1;
arr_column2     t_column2;

TYPE t_columnA IS TABLE OF ADDRESS.columnA%TYPE;
TYPE t_columnB IS TABLE OF ADDRESS.columnA%TYPE;

arr_columnA t_columnA := t_columnA();
arr_columnB t_columnB := t_columnB();

CURSOR cur IS
    SELECT column1,column2
    FROM USERS;

i       INTEGER;
l_done  BOOLEAN;
indx    INTEGER;

MAIN CODE:

OPEN cur;
LOOP
    FETCH cur BULK COLLECT INTO
    arr_column1, arr_column2
    LIMIT 10000;

    l_done := curRESALEMASTER%NOTFOUND;

    FOR indx IN 1 .. arr_column1.COUNT
    LOOP
        arr_columnA.extend;
        arr_columnB.extend;

        arr_columnA(indx) := arr_column1(indx);
        arr_columnB(indx) := 'XYZ';

    END LOOP;

    FORALL i IN 1 .. arr_column1.COUNT
        INSERT INTO ADDRESS partition VALUES (
            arr_columnA(i),
            arr_columnB(i),
            arr_column2(i);

    EXIT WHEN (l_done);

END LOOP;
COMMIT;
CLOSE cur;

So my issue is how to put condition for FORALL?

It will be something like:

    FORALL i IN 1 .. arr_column1.COUNT
    IF arr_columnA(indx) <> NULL THEN
        INSERT INTO ADDRESS partition VALUES (
            arr_columnA(i),
            arr_columnB(i),
            arr_column2(i);
    END IF;

But I cannot put condition inside FORALL


Solution

  • First of all, you can't compare a NULL with a variable. You have to use

       IS [NOT] NULL
    

    To answer your question, I would try this:

     FORALL i IN arr_column1.FIRST .. arr_column1.LAST
            INSERT INTO ADDRESS partition
            SELECT
                arr_columnA(i),
                arr_columnB(i),
                arr_column2(i)
            FROM DUAL
            WHERE arr_columnA(indx) IS NOT NULL
      ;