CREATE OR REPLACE TYPE typ_arrChar IS TABLE OF VARCHAR2(4000);

DROP TABLE test_dy PURGE

CREATE TABLE test_dy (id VARCHAR2(512 CHAR), name VARCHAR2(512 CHAR), type VARCHAR2(128), CONSTRAINT testdy_id_pk PRIMARY KEY(id))

INSERT INTO test_dy VALUES('id1', 'Mark', 'Full-time')

INSERT INTO test_dy VALUES('id2', 'Mark', 'Part-time')

INSERT INTO test_dy VALUES('id3', 'Will', 'Full-time')

INSERT INTO test_dy VALUES('id4', 'Joe', 'Part-time')

COMMIT

CREATE OR REPLACE PROCEDURE testDy_V2_prc
(
    i_sql_1         IN      VARCHAR2,
    i_sql_2         IN      VARCHAR2
)
AS
    l_sql           VARCHAR2(32767);
    l_arId          typ_arrChar := typ_arrChar();
BEGIN
    dbms_output.put_line('START');

    dbms_output.put_line('i_sql_1: ' || i_sql_1);
    dbms_output.put_line('i_sql_2: ' || i_sql_2);

    l_sql := 'SELECT  id
            FROM test_dy
            WHERE
            ('  || CHR(10)
                || i_sql_1
                || CHR(10) ||
            ')
        ';
    dbms_output.put_line('l_sql: ' || l_sql);

    EXECUTE IMMEDIATE
        ' BEGIN
            EXECUTE IMMEDIATE ''' || l_sql || '''
                BULK COLLECT INTO :l_arId
                USING ' || i_sql_2 ||';
          END;
        ' USING OUT l_arId;

    IF (l_arId.COUNT>0) THEN
        FOR i IN 1 .. l_arId.COUNT
        LOOP
            dbms_output.put_line('id: ' || l_arId(i));
        END LOOP;
    ELSE
        dbms_output.put_line('No data');
    END IF;

    dbms_output.put_line('END');
EXCEPTION
WHEN others THEN
    dbms_output.put_line('From Error Stack => ' || SUBSTR (DBMS_UTILITY.FORMAT_ERROR_STACK,1,300) || CHR(10) ||
                    'From Error Backtrace => ' || SUBSTR (DBMS_UTILITY.FORMAT_ERROR_BACKTRACE,1,300));
    RAISE_APPLICATION_ERROR
            (-20207,'From Error Stack => ' || SUBSTR (DBMS_UTILITY.FORMAT_ERROR_STACK,1,300) || CHR(10) ||
                    'From Error Backtrace => ' || SUBSTR (DBMS_UTILITY.FORMAT_ERROR_BACKTRACE,1,300));
END testDy_V2_prc;

DECLARE
    l_sql_1                     VARCHAR2(32767);
    l_sql_2                     VARCHAR2(32767);
    l_array1                    typ_arrChar := typ_arrChar();
    l_array2                    typ_arrChar := typ_arrChar();
BEGIN
    -- Dynamically generated from Step#2
    l_sql_1 := ' name IN (SELECT column_value FROM TABLE(:1)) AND type IN (SELECT column_value FROM TABLE(:2)) ';

    l_array1.EXTEND;
    l_array1(l_array1.COUNT) := 'Mark';

    l_array2.EXTEND;
    l_array2(l_array2.COUNT) := 'Full-Time';

    -- Dynamically generated from Step#3
    l_sql_2 := 'l_array1, l_array2';

    -- Invoke procedure to return desired result
    testDy_V2_prc
    (
        i_sql_1         =>       l_sql_1,
        i_sql_2         =>       l_sql_2
    );
END;

ORA-20207: From Error Stack => ORA-06550: line 10, column 23:

PLS-00201: identifier 'L_ARRAY1' must be declared

ORA-06550: line 2, column 13:

PL/SQL: Statement ignored

From Error Backtrace => ORA-06512: at "<SCHEMA_NAME>.TESTDY_V2_PRC", line 25

ORA-06512: at "<SCHEMA_NAME>.TESTDY_V2_PRC", line 47

ORA-06512: at line 20

Error at Line: 11 Column: 0