Fetching Multiple Rows from Dynamic SELECT

View All Scripts Login to Run Script

Script Name Fetching Multiple Rows from Dynamic SELECT
Description This script shows you how to use both EXECUTE IMMEDIATE with BULK COLLECT and OPEN FOR with cursor variables to fetch multiple rows from a dynamically-constructed SELECT statement.
Area PL/SQL General
Contributor Steven Feuerstein
Created Monday April 04, 2016

Statement 1

CREATE TABLE employees AS SELECT * FROM hr.employees

Table created.

Statement 2
It's the simplest and most efficient way to fetch multiple rows from a dynamically-constructed SELECT. One caution: this sort of un-limited BULK COLLECT can result in the consumption of very large amounts of Process Global Area memory.
EXECUTE IMMEDIATE with BULK COLLECT
```
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; 
```
```
107
```

Statement 3

I can OPEN FOR a dynamically-constructed SELECT statement and then FETCH BULK COLLECT all the rows. This approach has the same possible problem as the previous one: un-restrained consumption of PGA memory.

Use a Cursor Variable and BULK COLLECT

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

Statement 4

I OPEN FOR a dynamically-constructed SELECT statement and then inside a loop, FETCH BULK COLLECT with LIMIT to return a maximum number of rows with each fetch. This approach avoids excessive consumption of PGA memory.

Use Cursor Variable and BULK COLLECT with LIMIT

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

50
50
7

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database