In Oracle SQL, using EXECUTE IMMEDIATE along with BULK COLLECT allows you to dynamically execute SQL statements and efficiently retrieve multiple rows into PL/SQL collections. Here’s a detailed explanation of how you can use these features specifically in the context of Oracle SQL, focusing on smaller datasets.

Key Concepts

1. EXECUTE IMMEDIATE: This command allows you to execute a dynamic SQL statement at runtime. It is particularly useful when the SQL statement cannot be determined until execution.

2. BULK COLLECT: This feature is used to fetch multiple rows from a SQL query into a collection (such as a PL/SQL array) in a single operation, minimizing context switches between the SQL and PL/SQL engines.

Examples

Here are three key examples of using EXECUTE IMMEDIATE with BULK COLLECT in Oracle SQL for smaller datasets:

1. Fetching Multiple Rows into a Collection

You can dynamically construct a SQL query and fetch the results into a PL/SQL collection using BULK COLLECT.

Example:

DECLARE
-- Define a PL/SQL collection type to hold employee names
TYPE emp_name_table IS TABLE OF VARCHAR2(100) INDEX BY PLS_INTEGER;
emp_names emp_name_table; -- Collection to store names
BEGIN
-- Dynamically execute the SQL query to fetch employee names
EXECUTE IMMEDIATE 'SELECT name FROM employees WHERE department_id = 10'
BULK COLLECT INTO emp_names;

-- Output the results
FOR i IN 1 .. emp_names.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE('Employee Name: ' || emp_names(i));
END LOOP;

END;

In this example, we are executing a simple SELECT statement that fetches employee names for a specific department (ID = 10) and storing those names in the emp_names collection.

1. Fetching Multiple Columns into a Collection of Records

When you want to fetch multiple columns, you can define a record type and a collection to hold these records.

Example:

DECLARE
-- Define a record type to hold employee details
TYPE emp_record IS RECORD (
emp_id NUMBER,
emp_name VARCHAR2(100)
);

-- Define a collection type for the employee records
TYPE emp_table IS TABLE OF emp_record INDEX BY PLS_INTEGER;
emp_list emp_table;  -- Collection to store employee records

BEGIN
-- Execute the SQL query to fetch employee IDs and names
EXECUTE IMMEDIATE 'SELECT employee_id, name FROM employees WHERE department_id = 10'
BULK COLLECT INTO emp_list;

-- Output the results
FOR i IN 1 .. emp_list.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE('Employee ID: ' || emp_list(i).emp_id || ', Name: ' || emp_list(i).emp_name);
END LOOP;

END;

In this example, we define a RECORD type emp_record to hold employee details and fetch both the employee ID and name into the emp_list collection.

1. Using Bind Variables for Security and Performance

Even for smaller datasets, it’s a best practice to use bind variables. This helps prevent SQL injection and can enhance performance by allowing Oracle to reuse execution plans.

Example:

DECLARE
TYPE emp_name_table IS TABLE OF VARCHAR2(100) INDEX BY PLS_INTEGER;
emp_names emp_name_table; -- Collection to store names
v_dept_id NUMBER := 10; -- Bind variable for department ID
BEGIN
-- Execute the SQL query with a bind variable
EXECUTE IMMEDIATE 'SELECT name FROM employees WHERE department_id = :dept_id'
BULK COLLECT INTO emp_names USING v_dept_id;

-- Output the results
FOR i IN 1 .. emp_names.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE('Employee Name: ' || emp_names(i));
END LOOP;

END;

In this example, we use the bind variable :dept_id to securely pass the department ID into the dynamic SQL query.

Conclusion

Using EXECUTE IMMEDIATE with BULK COLLECT in Oracle SQL is a powerful way to dynamically execute SQL statements and efficiently retrieve multiple rows into collections. This approach is applicable even for smaller datasets and is best combined with bind variables to enhance security and performance.