Oracle BULK COLLECT INTO using EXECUTE IMMEDIATE

SET SERVEROUTPUT ON;

--Create a sample table
CREATE TABLE EMP(EMP_ID NUMBER, EMP_NAME VARCHAR2(20), AGE NUMBER(3))
/

--Insert a few records
INSERT INTO EMP (EMP_ID, EMP_NAME, AGE) VALUES (100, 'Regina Hall', 25)
/
INSERT INTO EMP (EMP_ID, EMP_NAME, AGE) VALUES (101, 'Morris Chestnut', 35)
/
INSERT INTO EMP (EMP_ID, EMP_NAME, AGE) VALUES (102, 'Terrence Howard', 37)
/
INSERT INTO EMP (EMP_ID, EMP_NAME, AGE) VALUES (103, 'Sanaa Lathan', 25)
/

--Create User defined Objects to use in BULK COLLECT
CREATE TYPE EMP_OBJ IS OBJECT (EMP_ID NUMBER, EMP_NAME VARCHAR2(20), AGE NUMBER(3))
/
CREATE TYPE EMP_TAB IS TABLE OF EMP_OBJ
/

--Anonymous block demonstrate the use of EXECUTE IMMEDIATE and BULK COLLECT INTO
DECLARE
    c_emp EMP_TAB;
    age_v NUMBER(3) :=25;
    query VARCHAR2(4000) := 'SELECT EMP_OBJ(EMP_ID, EMP_NAME, AGE) FROM EMP WHERE AGE=';
BEGIN
    query := query||age_v;
    EXECUTE IMMEDIATE query BULK COLLECT INTO c_emp;
    DBMS_OUTPUT.PUT_LINE('The number of employees in the Collection->'||c_emp.COUNT);
    FOR i IN c_emp.FIRST..c_emp.LAST
    LOOP
        DBMS_OUTPUT.PUT_LINE(c_emp(i).emp_id||','||c_emp(i).emp_name||','||c_emp(i).age);
    END LOOP;
END;
/

Here is the output:
The number of employees in the Collection->2
100,Regina Hall,25
103,Sanaa Lathan,25

PL/SQL procedure successfully completed.