CREATE TABLE employees 
AS 
   SELECT * FROM hr.employees 

Concatenate Table Name, Bind One Variable, Return Two Values

DECLARE  
   l_last_name   employees.last_name%TYPE;  
   l_salary      employees.salary%TYPE;  
  
   PROCEDURE show_value (table_in IN VARCHAR2, id_in IN INTEGER)  
   IS  
   BEGIN  
      EXECUTE IMMEDIATE  
            'SELECT last_name, salary FROM '  
         || sys.DBMS_ASSERT.sql_object_name (table_in)  
         || ' WHERE employee_id = :id_value'  
         INTO l_last_name, l_salary  
         USING id_in;  
  
      DBMS_OUTPUT.put_line (l_last_name || ' Earning ' || l_salary);  
   END;  
BEGIN  
   show_value ('EMPLOYEES', 138);  
END; 

DECLARE 
   l_employee   employees%ROWTYPE; 
BEGIN 
   EXECUTE IMMEDIATE 'SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID = :empid' 
      INTO l_employee 
      USING 138; 
 
   DBMS_OUTPUT.put_line (l_employee.last_name); 
END allrows_by; 

Use BULK COLLECT with EXECUTE IMMEDIATE

DECLARE  
   TYPE employee_ntt IS TABLE OF employees%ROWTYPE;  
  
   l_employees   employee_ntt;  
BEGIN  
   EXECUTE IMMEDIATE 'SELECT * FROM employees' BULK COLLECT INTO l_employees;  
  
   DBMS_OUTPUT.put_line (l_employees.COUNT);  
END allrows_by; 

Use OPEN FOR to Fetch From Dynamic SELECT

DECLARE  
   l_cursor      SYS_REFCURSOR;  
  
   TYPE employee_ntt IS TABLE OF employees%ROWTYPE;  
  
   l_employees   employee_ntt;  
BEGIN  
   OPEN l_cursor FOR 'SELECT * FROM employees';  
  
   FETCH l_cursor BULK COLLECT INTO l_employees;  
  
   DBMS_OUTPUT.put_line (l_employees.COUNT);  
  
   CLOSE l_cursor;  
END allrows_by; 

And With a LIMIT Clause

DECLARE  
   l_cursor      SYS_REFCURSOR;  
  
   TYPE employee_ntt IS TABLE OF employees%ROWTYPE;  
  
   l_employees   employee_ntt;  
BEGIN  
   OPEN l_cursor FOR 'SELECT * FROM employees';  
  
   LOOP  
      FETCH l_cursor BULK COLLECT INTO l_employees LIMIT 100;  
  
      EXIT WHEN l_employees.COUNT = 0;  
  
      DBMS_OUTPUT.put_line (l_employees.COUNT);  
   END LOOP;  
  
   CLOSE l_cursor;  
END allrows_by;