CREATE OR REPLACE PACKAGE my_pkg
IS
    FUNCTION my_fnc ( p1 VARCHAR2
                    , p2 VARCHAR2) RETURN VARCHAR2;
END my_pkg;
/
CREATE OR REPLACE PACKAGE BODY my_pkg
IS
    FUNCTION my_fnc ( p1 VARCHAR2
                    , p2 VARCHAR2) RETURN VARCHAR2
    IS
    BEGIN
        RETURN p1 || '-' || p2;
    END my_fnc;
END my_pkg;
/
CREATE OR REPACE PROCEURE my_prc
IS
    TYPE t_type IS TABLE OF my_tab2%ROWTYPE;
    v_nt t_type;
    v_colx my_tab2.colx%TYPE;
BEGIN
    -- version 1
    PRAGMA INLINE (my_fnc, 'YES');
    MERGE INTO my_tab1 a
    USING (SELECT col1
                , col2
                , my_pkg.my_fnc(col3, col4) colx
                , col5
                , col6
             FROM my_tab2
           WHERE 1 = 1) b -- the condition doesn't mather
       ON (a.col1 = b.col1 AND a.col2 = b.col2 AND a.colx = b.colx)
    WHEN MATCHED THEN
        UPDATE SET a.col5 = b.col5
                 , a.col6 = b.col6
    WHEN NOT MATCHED THEN
        INSERT ( col1
               , col2
               , colx
               , col5
               , col6);
    COMMIT;
    --
    -- version 2
    SELECT col1
         , col2
         , my_pkg.my_fnc(col3, col4) colx
         , col5
         , col6
    BULK COLLECT INTO v_nt
    FROM my_tab2;
    
    FORALL i IN v_nt.FIRST .. v_nt.LAST
        PRAGMA INLINE (my_fnc, 'YES');
        v_colx := my_pkg.my_fnc(col3, col4);
        
        MERGE INTO my_tab1 a
        USING (SELECT v_nt(i).col1 col1
                    , v_nt(i).col2 col2
                    , v_colx
                    , v_nt(i).col5 col5
                    , v_nt(i).col6 col6
                 FROM dual) b -- the condition doesn't mather
       ON (a.col1 = b.col1 AND a.col2 = b.col2 AND a.colx = b.colx)
    WHEN MATCHED THEN
        UPDATE SET a.col5 = b.col5
                 , a.col6 = b.col6
    WHEN NOT MATCHED THEN
        INSERT ( col1
               , col2
               , colx
               , col5
               , col6);
END my_prc;