Как выполнить условную обработку в цикле массового сбора?

• Условие ЕСЛИ не допускается внутри FOR ALL.
• FOR ALL может выполнять один оператор DML: INSERT, UPDATE или DELETE, который записывается после него. Это не нормально для цикла.

Вы можете попробовать следующий код:

Упаковка:

CREATE OR REPLACE PACKAGE MYSCHEMA.MYPKG AS
    CURSOR CUR_MYDATA IS
    SELECT
        O.NAME,
        O.LAST_NAME,
        O.ID,
        -- ADDED THIS CASE STATEMENT
        CASE
            WHEN O.SOCNUM IS NOT NULL THEN O.SOCNUM
            ELSE OO.SOCNUM
        END AS SOCNUM
    FROM
        -- ADDED THIS LEF JOIN
        ORIGIN O
        LEFT JOIN OTHER_ORIGIN OO ON ( OO.ID = O.ID )
    WHERE
        1 = 1
        AND O.NAME LIKE UPPER('a%');

    TYPE T_NAME IS
        TABLE OF ORIGIN.NAME%TYPE;
    TYPE T_LASTNAME IS
        TABLE OF ORIGIN.LAST_NAME%TYPE;
    TYPE T_ID IS
        TABLE OF ORIGIN.ID%TYPE;
    TYPE T_SOCNUM IS
        TABLE OF ORIGIN.SOCNUM%TYPE;
    L_NAME T_NAME;
    L_LASTNAME T_LASTNAME;
    L_ID T_ID;
    L_SOCNUM T_SOCNUM;
    PROCEDURE MYPROCEDURE;

END MYPKG;

Тело пакета

CREATE OR REPLACE PACKAGE BODY MYSCHEMA.MYPKG AS

    PROCEDURE MYPROCEDURE IS
    BEGIN
        OPEN CUR_MYDATA;
        FETCH CUR_MYDATA BULK COLLECT INTO
            L_NAME,
            L_LASTNAME,
            L_ID,
            L_SOCNUM
        LIMIT 1000;
        FORALL I IN 1..L_NAME.COUNT
--
-- REMOVED THIS CONDITION
--
--            IF ( l_socnum(i) IS NULL) 
--                THEN (select oo.socnum from other_origin where oo.id=l_id(i)) 
--            END IF;
            INSERT INTO DESTINY (
                D_NAME,
                D_LASTNAME,
                D_ID,
                D_SOCNUM
            ) VALUES (
                L_NAME(I),
                L_LASTNAME(I),
                L_ID(I),
                L_SOCNUM(I)
            );

        CLOSE CUR_MYDATA;
    END MYPROCEDURE;

END MYPKG;

FORALL не является конструкцией цикла: ее нельзя отделить от инструкции DML.

when I tried to evaluate if one field from origin is empty

Вам нужно зациклиться на заполненной коллекции и исправить это перед выполнением FORALL... INSERT.

CREATE OR REPLACE PACKAGE BODY MYSCHEMA.MYPKG AS

    PROCEDURE MYPROCEDURE IS

    BEGIN
        OPEN CUR_MYDATA;
        LOOP
            FETCH CUR_MYDATA BULK COLLECT INTO l_name,l_lastname,l_id,l_socnum;
            EXIT WHEN l_name.count = 0;

            for idx in 1 .. l_socnum.count() loop
                IF l_socnum(idx) IS NULL THEN
                      select oo.socnum 
                      into l_socnum(idx)
                      from other_origin 
                      where oo.id = l_id(idx);
                END IF;

            end loop;

            forall i IN 1 .. l_name.COUNT
                INSERT INTO destiny (
                    d_name,
                    d_lastname,
                    d_id,
                    d_socnum) 
                VALUES (
                    l_name(i),
                    l_lastname(i),
                    l_id(i),
                    l_socnum(i));
        END LOOP;
    END MYPROCEDURE;

END MYPKG; 

Другие примечания.

1. Проверьте, возвращает ли выборка какие-либо записи сразу после выполнения выборки. В противном случае ваш код попытается выполнить код над пустой коллекцией, что завершится ошибкой.
2. Вы должны определить коллекцию на основе целевой таблицы %rowtype: это проще, чем определение и обработка нескольких коллекций на основе столбцов.

Кроме того, ваш реальный код может быть намного сложнее, чем то, что вы разместили здесь, но если у вас есть большой объем данных для переноса, использование чистого SQL, а не процедуры, дает значительный прирост производительности:

INSERT INTO DESTINY (
            D_NAME,
            D_LASTNAME,
            D_ID,
            D_SOCNUM
        ) 
SELECT
        o.name,
        o.last_name,
        o.id,
        coalesce(o.socnum, oo.socnum)
FROM
    origin o
left outer join other_origin oo 
   on oo.id = o.id
WHERE
    1=1
    AND o.name like upper ('a%');