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', 'Will', 'Full-time')

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

COMMIT

CREATE OR REPLACE PROCEDURE testDy_prc
(
    i_sql_1         IN      VARCHAR2,
    i_sql_2         IN      VARCHAR2
)
AS
    l_sql           VARCHAR2(32767);
    l_arId          typ_arrChar := typ_arrChar();
    l_arName        typ_arrChar := typ_arrChar();
    l_arType        typ_arrChar := typ_arrChar();
BEGIN
    l_sql := 'SELECT  id,
                    name,
                    type
            FROM test_dy
            WHERE
            ('  || CHR(10)
                || i_sql_1
                || CHR(10) ||
            ')
            ORDER BY name asc
            OFFSET 0 ROWS FETCH NEXT 50 ROWS ONLY
        ';

    EXECUTE IMMEDIATE
        ' BEGIN
            EXECUTE IMMEDIATE ''' || l_sql || '''
                BULK COLLECT INTO l_arId, l_arName, l_arType
                USING ' || i_sql_2 ||';
          END;
        ';

    IF (l_arName.COUNT>0) THEN
        FOR i IN 1 .. l_arName.COUNT
        LOOP
            dbms_output.put_line('name: ' || l_arName(i));
        END LOOP;
    END IF;
EXCEPTION
WHEN others THEN
    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_prc;

DECLARE
    l_sql_1                     VARCHAR2(32767);
    l_sql_2                     VARCHAR2(32767);
BEGIN
    -- Dynamically generated from Step#2
    l_sql_1 := 'LOWER(name) LIKE LOWER(:3)   OR LOWER(name) LIKE LOWER(:5)';

    -- Dynamically generated from Step#3
    l_sql_2 := '''%Mark%'', ''Will%''';

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

ORA-20207: From Error Stack => ORA-06550: line 13, column 35:

PLS-00201: identifier 'L_ARID' must be declared

ORA-06550: line 2, column 13:

PL/SQL: Statement ignored

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

ORA-06512: at "<SCHEMA_NAME>.TESTDY_PRC", line 41

ORA-06512: at line 12

Error at Line: 11 Column: 0