The EXECUTE IMMEDIATE Statement

The EXECUTE IMMEDIATE statement is the cornerstone of Native Dynamic SQL (NDS) in Oracle PL/SQL. It allows you to execute SQL statements and PL/SQL blocks that are constructed as character strings at runtime. This means you can create highly flexible and generic code that can adapt to different scenarios without being hardcoded. Think of it as telling Oracle, "Here's a piece of SQL I just built, please run it for me now!"

Example 1: Executing a DDL Statement with EXECUTE IMMEDIATE

This example demonstrates how to create a table dynamically using EXECUTE IMMEDIATE. This is often useful in administrative scripts or installation routines where table names might be user-defined.

DECLARE
    v_table_name VARCHAR2(100) := 'EMPLOYEE_AUDIT_' || TO_CHAR(SYSDATE, 'YYYYMMDD');
    v_sql_stmt   VARCHAR2(500);
BEGIN
    -- Construct the DDL statement dynamically
    v_sql_stmt := 'CREATE TABLE ' || v_table_name || ' ( ' ||
                  '  audit_id NUMBER PRIMARY KEY, ' ||
                  '  employee_id NUMBER, ' ||
                  '  change_date DATE, ' ||
                  '  description VARCHAR2(255) ' ||
                  ')';

    -- Execute the DDL statement
    EXECUTE IMMEDIATE v_sql_stmt;

    DBMS_OUTPUT.PUT_LINE('Table ' || v_table_name || ' created successfully.');

EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error creating table: ' || SQLERRM);
END;
/

Explanation

In this example, we declare two variables: v_table_name to hold the dynamically generated table name (including a date for uniqueness) and v_sql_stmt to store the complete CREATE TABLE statement. We concatenate strings to build the CREATE TABLE DDL statement. Finally, EXECUTE IMMEDIATE v_sql_stmt; executes the constructed SQL string, creating the new table. This demonstrates the power of EXECUTE IMMEDIATE for dynamic Data Definition Language (DDL) operations.

Example 2: Executing a DML Statement with EXECUTE IMMEDIATE

Here, we'll see how to perform a dynamic INSERT operation. This can be handy when you need to insert data into different tables or columns based on certain conditions.

DECLARE
    v_table_name VARCHAR2(100) := 'AUDIT_LOG';
    v_column1    VARCHAR2(50)  := 'log_id';
    v_column2    VARCHAR2(50)  := 'log_message';
    v_value1     NUMBER        := 1001;
    v_value2     VARCHAR2(200) := 'User login event.';
    v_sql_stmt   VARCHAR2(500);
BEGIN
    -- Assume AUDIT_LOG table already exists with log_id and log_message columns
    -- For demonstration, let's create it if it doesn't exist
    BEGIN
        EXECUTE IMMEDIATE 'CREATE TABLE AUDIT_LOG (log_id NUMBER, log_message VARCHAR2(200))';
    EXCEPTION
        WHEN OTHERS THEN
            NULL; -- Table likely already exists
    END;

    -- Construct the DML (INSERT) statement dynamically
    v_sql_stmt := 'INSERT INTO ' || v_table_name || ' (' || v_column1 || ', ' || v_column2 || ') ' ||
                  'VALUES (:1, :2)'; -- Using bind variables for security and performance

    -- Execute the DML statement with bind variables
    EXECUTE IMMEDIATE v_sql_stmt USING v_value1, v_value2;

    DBMS_OUTPUT.PUT_LINE('Row inserted into ' || v_table_name || ' successfully.');

    -- Optional: Verify the insert
    FOR rec IN (SELECT log_id, log_message FROM AUDIT_LOG WHERE log_id = v_value1) LOOP
        DBMS_OUTPUT.PUT_LINE('Verified: ID=' || rec.log_id || ', Message=' || rec.log_message);
    END LOOP;

EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error inserting data: ' || SQLERRM);
END;
/

Explanation

This example demonstrates a dynamic INSERT statement. We build the INSERT string, including the table and column names dynamically. Crucially, notice the use of :1 and :2 as placeholders for bind variables. This is a best practice for dynamic SQL as it prevents SQL injection vulnerabilities and improves performance by allowing Oracle to reuse parsed statements. The USING clause (which we'll discuss in more detail shortly) provides the actual values for these placeholders.

Example 3: Executing a Dynamic PL/SQL Block

EXECUTE IMMEDIATE isn't just for SQL statements; you can also execute entire PL/SQL blocks dynamically. This is incredibly powerful for complex scenarios where you need to run custom logic based on runtime conditions.

DECLARE
    v_plsql_block VARCHAR2(1000);
    v_message     VARCHAR2(100) := 'Hello from dynamic PL/SQL!';
    v_result      VARCHAR2(100);
BEGIN
    -- Construct a dynamic PL/SQL block
    -- The PL/SQL block assigns a value to an OUT bind variable
    v_plsql_block := 'BEGIN :result := :message || '' - Processed''; END;';

    -- Execute the dynamic PL/SQL block with IN and OUT bind variables
    EXECUTE IMMEDIATE v_plsql_block USING OUT v_result, IN v_message;

    DBMS_OUTPUT.PUT_LINE('Original message: ' || v_message);
    DBMS_OUTPUT.PUT_LINE('Result from dynamic PL/SQL: ' || v_result);

EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error executing dynamic PL/SQL: ' || SQLERRM);
END;
/

Explanation

Here, we construct a PL/SQL anonymous block as a string. This block takes an IN parameter (:message) and an OUT parameter (:result). When EXECUTE IMMEDIATE is called, the USING clause specifies both the OUT and IN variables. The dynamic PL/SQL block executes, modifies the result variable, and that modified value is then available in the v_result variable in the calling block.

Example 4: Dynamic UPDATE Statement

This example shows how to build and execute a dynamic UPDATE statement. This can be useful for generic update routines where the target table, columns, and conditions might vary.

DECLARE
    v_table_name    VARCHAR2(100) := 'EMPLOYEES'; -- Assuming EMPLOYEES table exists
    v_set_column    VARCHAR2(50)  := 'salary';
    v_where_column  VARCHAR2(50)  := 'employee_id';
    v_new_salary    NUMBER        := 65000;
    v_employee_id   NUMBER        := 101;
    v_sql_stmt      VARCHAR2(500);
BEGIN
    -- For demonstration, ensure a row exists
    BEGIN
        EXECUTE IMMEDIATE 'INSERT INTO EMPLOYEES (employee_id, salary) VALUES (101, 60000)';
    EXCEPTION
        WHEN DUP_VAL_ON_INDEX THEN
            NULL; -- Employee already exists
    END;

    -- Construct the dynamic UPDATE statement
    v_sql_stmt := 'UPDATE ' || v_table_name ||
                  ' SET ' || v_set_column || ' = :new_value ' ||
                  ' WHERE ' || v_where_column || ' = :where_value';

    -- Execute the UPDATE statement with bind variables
    EXECUTE IMMEDIATE v_sql_stmt USING v_new_salary, v_employee_id;

    DBMS_OUTPUT.PUT_LINE('Employee ' || v_employee_id || ' salary updated to ' || v_new_salary || ' successfully.');

    -- Verify the update
    DECLARE
        current_salary NUMBER;
    BEGIN
        EXECUTE IMMEDIATE 'SELECT salary FROM EMPLOYEES WHERE employee_id = :id' INTO current_salary USING v_employee_id;
        DBMS_OUTPUT.PUT_LINE('Verified current salary for ' || v_employee_id || ': ' || current_salary);
    END;

EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error updating data: ' || SQLERRM);
END;
/

Explanation

This example constructs an UPDATE statement dynamically, allowing the table, column to be updated, and the WHERE clause column to be specified at runtime. Again, bind variables (:new_value, :where_value) are used to pass the new salary and employee ID safely and efficiently. After the update, a dynamic SELECT is used to verify the change, demonstrating how dynamic SQL can be used for both DML and DQL.

Example 5: Dynamic DELETE Statement

Finally, let's look at a dynamic DELETE statement. This is useful for cleanup operations or when you need to remove data based on flexible criteria.

DECLARE
    v_table_name    VARCHAR2(100) := 'TEMP_DATA';
    v_where_column  VARCHAR2(50)  := 'status';
    v_where_value   VARCHAR2(50)  := 'OBSOLETE';
    v_sql_stmt      VARCHAR2(500);
    v_rows_deleted  NUMBER;
BEGIN
    -- For demonstration, create and insert some data
    BEGIN
        EXECUTE IMMEDIATE 'CREATE TABLE TEMP_DATA (id NUMBER, status VARCHAR2(50))';
    EXCEPTION
        WHEN OTHERS THEN
            NULL; -- Table likely already exists
    END;
    EXECUTE IMMEDIATE 'INSERT INTO TEMP_DATA (id, status) VALUES (1, ''ACTIVE'')';
    EXECUTE IMMEDIATE 'INSERT INTO TEMP_DATA (id, status) VALUES (2, ''OBSOLETE'')';
    EXECUTE IMMEDIATE 'INSERT INTO TEMP_DATA (id, status) VALUES (3, ''ACTIVE'')';
    EXECUTE IMMEDIATE 'INSERT INTO TEMP_DATA (id, status) VALUES (4, ''OBSOLETE'')';
    COMMIT;

    DBMS_OUTPUT.PUT_LINE('Initial row count for OBSOLETE status:');
    DECLARE
        initial_count NUMBER;
    BEGIN
        EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM TEMP_DATA WHERE status = :status' INTO initial_count USING v_where_value;
        DBMS_OUTPUT.PUT_LINE('Count: ' || initial_count);
    END;

    -- Construct the dynamic DELETE statement
    v_sql_stmt := 'DELETE FROM ' || v_table_name ||
                  ' WHERE ' || v_where_column || ' = :delete_value';

    -- Execute the DELETE statement with a bind variable
    EXECUTE IMMEDIATE v_sql_stmt USING v_where_value;

    -- Get the number of rows deleted using SQL%ROWCOUNT
    v_rows_deleted := SQL%ROWCOUNT;

    DBMS_OUTPUT.PUT_LINE(v_rows_deleted || ' rows deleted from ' || v_table_name || ' where ' || v_where_column || ' = ''' || v_where_value || ''' successfully.');

    -- Verify the deletion
    DBMS_OUTPUT.PUT_LINE('Final row count for OBSOLETE status:');
    DECLARE
        final_count NUMBER;
    BEGIN
        EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM TEMP_DATA WHERE status = :status' INTO final_count USING v_where_value;
        DBMS_OUTPUT.PUT_LINE('Count: ' || final_count);
    END;

EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error deleting data: ' || SQLERRM);
END;
/

Explanation

This example demonstrates a dynamic DELETE operation. The table name, the column to filter on, and the value for the filter are all provided dynamically. The SQL%ROWCOUNT attribute is used after EXECUTE IMMEDIATE to determine how many rows were affected by the DELETE statement, which is a common practice in DML operations. We also include checks for initial and final row counts to clearly illustrate the effect of the dynamic delete.

The USING Clause for Bind Variables

The USING clause is an integral part of EXECUTE IMMEDIATE when your dynamic SQL statement contains bind variables. Bind variables are placeholders (like :1, :name, etc.) in your SQL string that you later associate with actual PL/SQL variables or expressions. The USING clause provides a secure and efficient way to pass data into your dynamic SQL statements. This is crucial for preventing SQL injection attacks and improving performance by allowing Oracle to parse and cache SQL statements more effectively.

Example 1: Single IN Bind Variable

This example shows how to pass a single input value into a dynamic SELECT statement using the USING clause.

DECLARE
    v_employee_id NUMBER := 100;
    v_employee_name VARCHAR2(100);
    v_sql_stmt    VARCHAR2(200);
BEGIN
    -- Assume EMPLOYEES table exists with employee_id and employee_name
    -- For demonstration, insert a sample record if not present
    BEGIN
        EXECUTE IMMEDIATE 'INSERT INTO EMPLOYEES (employee_id, employee_name) VALUES (100, ''John Doe'')';
    EXCEPTION
        WHEN DUP_VAL_ON_INDEX THEN NULL;
    END;

    -- Construct the dynamic SELECT statement with a bind variable
    v_sql_stmt := 'SELECT employee_name FROM EMPLOYEES WHERE employee_id = :emp_id';

    -- Execute the dynamic SELECT using the INTO clause for the result
    -- and the USING clause for the bind variable.
    EXECUTE IMMEDIATE v_sql_stmt INTO v_employee_name USING v_employee_id;

    DBMS_OUTPUT.PUT_LINE('Employee Name for ID ' || v_employee_id || ': ' || v_employee_name);

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('No employee found with ID: ' || v_employee_id);
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/

Explanation

Here, v_sql_stmt contains a SELECT statement with a placeholder :emp_id. The USING v_employee_id clause in the EXECUTE IMMEDIATE statement tells Oracle to substitute the value of v_employee_id for :emp_id before executing the query. This is a fundamental way to pass input parameters to dynamic SQL.

Example 2: Multiple IN Bind Variables

You can pass multiple input values to your dynamic SQL by listing them in the USING clause, separated by commas. The order of variables in the USING clause corresponds to the order of placeholders in your SQL string (if using positional bind variables like :1, :2).

DECLARE
    v_first_name  VARCHAR2(50)  := 'Jane';
    v_last_name   VARCHAR2(50)  := 'Smith';
    v_email       VARCHAR2(100) := 'jane.smith@example.com';
    v_sql_stmt    VARCHAR2(500);
BEGIN
    -- Assume EMPLOYEES table exists with first_name, last_name, email
    -- For demonstration, create the table if it doesn't exist
    BEGIN
        EXECUTE IMMEDIATE 'CREATE TABLE EMPLOYEES (first_name VARCHAR2(50), last_name VARCHAR2(50), email VARCHAR2(100))';
    EXCEPTION
        WHEN OTHERS THEN NULL;
    END;

    -- Construct a dynamic INSERT statement with multiple bind variables
    v_sql_stmt := 'INSERT INTO EMPLOYEES (first_name, last_name, email) ' ||
                  'VALUES (:fn, :ln, :em)';

    -- Execute the INSERT statement using the USING clause for all input values
    EXECUTE IMMEDIATE v_sql_stmt USING v_first_name, v_last_name, v_email;

    DBMS_OUTPUT.PUT_LINE('New employee ' || v_first_name || ' ' || v_last_name || ' inserted.');

    -- Verify the insert
    DECLARE
        v_check_email VARCHAR2(100);
    BEGIN
        EXECUTE IMMEDIATE 'SELECT email FROM EMPLOYEES WHERE first_name = :fn AND last_name = :ln'
        INTO v_check_email USING v_first_name, v_last_name;
        DBMS_OUTPUT.PUT_LINE('Verified email for ' || v_first_name || ' ' || v_last_name || ': ' || v_check_email);
    END;

EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error inserting employee: ' || SQLERRM);
END;
/

Explanation

In this example, we're performing a dynamic INSERT into the EMPLOYEES table. The v_sql_stmt uses named bind variables (:fn, :ln, :em). The USING clause then maps the PL/SQL variables v_first_name, v_last_name, and v_email to these bind variables in the order they appear in the USING clause.

Example 3: IN OUT Bind Variable

The USING clause also supports IN OUT bind variables, which are variables that are passed into the dynamic SQL and then potentially modified and passed back out.

DECLARE
    v_counter    NUMBER := 5;
    v_sql_stmt   VARCHAR2(200);
BEGIN
    -- Construct a dynamic PL/SQL block that increments a counter
    v_sql_stmt := 'BEGIN :counter := :counter + 10; END;';

    -- Execute the dynamic PL/SQL block with an IN OUT bind variable
    EXECUTE IMMEDIATE v_sql_stmt USING IN OUT v_counter;

    DBMS_OUTPUT.PUT_LINE('Counter after dynamic PL/SQL: ' || v_counter); -- Expected: 15

EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error with IN OUT bind variable: ' || SQLERRM);
END;
/

Explanation

Here, v_counter is initially 5. The dynamic PL/SQL block increments v_counter by 10. By specifying IN OUT v_counter in the USING clause, the initial value of v_counter is passed into the dynamic block, and the modified value from the dynamic block is passed back out to the v_counter variable in the calling scope.

Example 4: OUT Bind Variable for Function Return Value

You can use an OUT bind variable to capture a return value from a dynamic PL/SQL function call or a SELECT statement within a PL/SQL block.

DECLARE
    v_current_date DATE;
    v_sql_stmt     VARCHAR2(200);
BEGIN
    -- Construct a dynamic PL/SQL block that returns the current date
    v_sql_stmt := 'BEGIN :out_date := SYSDATE; END;';

    -- Execute the dynamic PL/SQL block, capturing the output into v_current_date
    EXECUTE IMMEDIATE v_sql_stmt USING OUT v_current_date;

    DBMS_OUTPUT.PUT_LINE('Current Date from dynamic PL/SQL: ' || TO_CHAR(v_current_date, 'YYYY-MM-DD HH24:MI:SS'));

EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error with OUT bind variable: ' || SQLERRM);
END;
/

Explanation

This example demonstrates how to use an OUT bind variable to retrieve a value from a dynamic PL/SQL block. The dynamic block simply assigns SYSDATE to the :out_date bind variable. The USING OUT v_current_date clause ensures that the value assigned to :out_date inside the dynamic block is returned and stored in v_current_date.

Example 5: Mixed IN and OUT Bind Variables

It's common to have a mix of IN and OUT bind variables within the same EXECUTE IMMEDIATE call.

DECLARE
    v_input_number  NUMBER := 10;
    v_output_square NUMBER;
    v_sql_stmt      VARCHAR2(200);
BEGIN
    -- Construct a dynamic PL/SQL block that squares an input number
    v_sql_stmt := 'BEGIN :output_val := :input_val * :input_val; END;';

    -- Execute the dynamic PL/SQL block with an IN and an OUT bind variable
    EXECUTE IMMEDIATE v_sql_stmt USING OUT v_output_square, IN v_input_number;

    DBMS_OUTPUT.PUT_LINE('Input Number: ' || v_input_number);
    DBMS_OUTPUT.PUT_LINE('Output Square: ' || v_output_square);

EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error with mixed IN/OUT bind variables: ' || SQLERRM);
END;
/

Explanation

Here, we have an IN variable v_input_number and an OUT variable v_output_square. The dynamic PL/SQL block performs a calculation using the IN variable and assigns the result to the OUT variable. The order in the USING clause matters: OUT variables are typically listed before IN variables if both are present for clarity, but the actual order of declaration in the dynamic statement (e.g., :output_val := :input_val * :input_val;) determines the flow. However, Oracle matches bind variables by their names or by their positional order if using numbers (e.g., :1, :2). It is important to match the types and directions correctly.

The INTO Clause for Single Rows

The INTO clause in EXECUTE IMMEDIATE is used when your dynamic SQL statement is a SELECT query that is expected to return exactly one row. If the query returns zero rows, a NO_DATA_FOUND exception is raised. If it returns more than one row, a TOO_MANY_ROWS exception is raised. It's essential for handling single-row queries dynamically.

Example 1: Retrieving a Single Column

This example demonstrates fetching a single column value from a dynamic SELECT statement.

DECLARE
    v_employee_id NUMBER := 102;
    v_employee_email VARCHAR2(100);
    v_sql_stmt    VARCHAR2(200);
BEGIN
    -- Assume EMPLOYEES table with employee_id and email
    -- For demonstration, insert a sample record
    BEGIN
        EXECUTE IMMEDIATE 'INSERT INTO EMPLOYEES (employee_id, employee_name, email) VALUES (102, ''Alice'', ''alice@example.com'')';
    EXCEPTION
        WHEN DUP_VAL_ON_INDEX THEN NULL;
    END;

    -- Construct the dynamic SELECT statement
    v_sql_stmt := 'SELECT email FROM EMPLOYEES WHERE employee_id = :id';

    -- Execute the dynamic SELECT using INTO to capture the result
    EXECUTE IMMEDIATE v_sql_stmt INTO v_employee_email USING v_employee_id;

    DBMS_OUTPUT.PUT_LINE('Email for Employee ID ' || v_employee_id || ': ' || v_employee_email);

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('No employee found with ID: ' || v_employee_id);
    WHEN TOO_MANY_ROWS THEN
        DBMS_OUTPUT.PUT_LINE('More than one employee found with ID: ' || v_employee_id || '. This should not happen for a unique ID.');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/

Explanation

Here, the SELECT statement is constructed to retrieve the email for a specific employee_id. The INTO v_employee_email clause directs the single fetched value into the v_employee_email PL/SQL variable. The USING clause provides the value for the bind variable.

Example 2: Retrieving Multiple Columns

You can fetch multiple columns into multiple PL/SQL variables using the INTO clause. The number and data types of the variables in the INTO clause must match the number and data types of the columns in the SELECT list.

DECLARE
    v_product_id    NUMBER := 1;
    v_product_name  VARCHAR2(100);
    v_price         NUMBER;
    v_sql_stmt      VARCHAR2(300);
BEGIN
    -- Assume PRODUCTS table with product_id, product_name, price
    -- For demonstration, insert a sample record
    BEGIN
        EXECUTE IMMEDIATE 'CREATE TABLE PRODUCTS (product_id NUMBER PRIMARY KEY, product_name VARCHAR2(100), price NUMBER)';
    EXCEPTION
        WHEN OTHERS THEN NULL;
    END;
    BEGIN
        EXECUTE IMMEDIATE 'INSERT INTO PRODUCTS (product_id, product_name, price) VALUES (1, ''Laptop'', 1200.00)';
    EXCEPTION
        WHEN DUP_VAL_ON_INDEX THEN NULL;
    END;

    -- Construct the dynamic SELECT statement for multiple columns
    v_sql_stmt := 'SELECT product_name, price FROM PRODUCTS WHERE product_id = :id';

    -- Execute the dynamic SELECT using INTO for multiple results
    EXECUTE IMMEDIATE v_sql_stmt INTO v_product_name, v_price USING v_product_id;

    DBMS_OUTPUT.PUT_LINE('Product Name: ' || v_product_name || ', Price: ' || v_price);

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('No product found with ID: ' || v_product_id);
    WHEN TOO_MANY_ROWS THEN
        DBMS_OUTPUT.PUT_LINE('More than one product found with ID: ' || v_product_id || '. This should not happen for a unique ID.');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/

Explanation

Here, the SELECT statement retrieves both product_name and price. The INTO v_product_name, v_price clause ensures that the values are assigned to the respective variables in the same order as they appear in the SELECT list.

Example 3: Retrieving into a Record Type

For convenience and readability, especially when dealing with many columns, you can retrieve a single row into a user-defined record type or a %ROWTYPE variable.

DECLARE
    TYPE ProductRec IS RECORD (
        product_name VARCHAR2(100),
        price        NUMBER
    );
    v_product_info ProductRec;
    v_product_id   NUMBER := 2;
    v_sql_stmt     VARCHAR2(300);
BEGIN
    -- Assume PRODUCTS table exists from previous example
    -- Insert another sample record
    BEGIN
        EXECUTE IMMEDIATE 'INSERT INTO PRODUCTS (product_id, product_name, price) VALUES (2, ''Mouse'', 25.50)';
    EXCEPTION
        WHEN DUP_VAL_ON_INDEX THEN NULL;
    END;

    -- Construct the dynamic SELECT statement
    v_sql_stmt := 'SELECT product_name, price FROM PRODUCTS WHERE product_id = :id';

    -- Execute the dynamic SELECT into a record type
    EXECUTE IMMEDIATE v_sql_stmt INTO v_product_info USING v_product_id;

    DBMS_OUTPUT.PUT_LINE('Product Name (Record): ' || v_product_info.product_name || ', Price (Record): ' || v_product_info.price);

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('No product found with ID: ' || v_product_id);
    WHEN TOO_MANY_ROWS THEN
        DBMS_OUTPUT.PUT_LINE('More than one product found with ID: ' || v_product_id || '. This should not happen for a unique ID.');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/

Explanation

This example demonstrates fetching a single row into a ProductRec record type. The INTO v_product_info clause automatically maps the selected columns to the corresponding fields in the record. This makes the code cleaner and easier to manage, especially with wider tables.

Example 4: Handling NO_DATA_FOUND and TOO_MANY_ROWS

It's crucial to handle the exceptions that EXECUTE IMMEDIATE ... INTO can raise.

DECLARE
    v_user_id   NUMBER := 999; -- An ID that likely won't exist
    v_user_name VARCHAR2(100);
    v_sql_stmt  VARCHAR2(200);
BEGIN
    -- Assume USERS table with user_id and user_name
    -- For demonstration, create and populate if not exists
    BEGIN
        EXECUTE IMMEDIATE 'CREATE TABLE USERS (user_id NUMBER PRIMARY KEY, user_name VARCHAR2(100))';
    EXCEPTION
        WHEN OTHERS THEN NULL;
    END;
    -- Insert a duplicate user_id to trigger TOO_MANY_ROWS later
    BEGIN
        EXECUTE IMMEDIATE 'INSERT INTO USERS (user_id, user_name) VALUES (1, ''Admin'')';
        EXECUTE IMMEDIATE 'INSERT INTO USERS (user_id, user_name) VALUES (1, ''SuperUser'')'; -- This will cause DUP_VAL_ON_INDEX for primary key, but for demonstration, assume no unique constraint for a moment. For a better demo, let's change search criteria
        EXECUTE IMMEDIATE 'INSERT INTO USERS (user_id, user_name) VALUES (10, ''UserA'')';
        EXECUTE IMMEDIATE 'INSERT INTO USERS (user_id, user_name) VALUES (20, ''UserB'')';
        EXECUTE IMMEDIATE 'INSERT INTO USERS (user_id, user_name) VALUES (20, ''UserC'')'; -- This will cause TOO_MANY_ROWS if searching by user_id = 20
    EXCEPTION
        WHEN DUP_VAL_ON_INDEX THEN NULL;
    END;
    COMMIT;


    DBMS_OUTPUT.PUT_LINE('--- Testing NO_DATA_FOUND ---');
    v_sql_stmt := 'SELECT user_name FROM USERS WHERE user_id = :id';
    BEGIN
        EXECUTE IMMEDIATE v_sql_stmt INTO v_user_name USING v_user_id;
        DBMS_OUTPUT.PUT_LINE('User Name for ID ' || v_user_id || ': ' || v_user_name);
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            DBMS_OUTPUT.PUT_LINE('EXPECTED: No user found with ID: ' || v_user_id);
    END;

    DBMS_OUTPUT.PUT_LINE('--- Testing TOO_MANY_ROWS ---');
    v_user_id := 20; -- This ID has multiple entries in our setup
    BEGIN
        EXECUTE IMMEDIATE v_sql_stmt INTO v_user_name USING v_user_id;
        DBMS_OUTPUT.PUT_LINE('User Name for ID ' || v_user_id || ': ' || v_user_name);
    EXCEPTION
        WHEN TOO_MANY_ROWS THEN
            DBMS_OUTPUT.PUT_LINE('EXPECTED: More than one user found with ID: ' || v_user_id);
    END;

EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('General Error: ' || SQLERRM);
END;
/

Explanation

This example explicitly demonstrates how to catch and handle NO_DATA_FOUND when the dynamic query returns no rows, and TOO_MANY_ROWS when it returns more than one. These are common scenarios with SELECT INTO, and proper error handling is vital for robust applications.

Example 5: Dynamic COUNT(*) with INTO

While not strictly retrieving a single row of data, COUNT(*) always returns a single value, making it a perfect candidate for EXECUTE IMMEDIATE ... INTO.

DECLARE
    v_table_name VARCHAR2(100) := 'EMPLOYEES';
    v_row_count  NUMBER;
    v_sql_stmt   VARCHAR2(200);
BEGIN
    -- Construct the dynamic COUNT statement
    v_sql_stmt := 'SELECT COUNT(*) FROM ' || v_table_name;

    -- Execute the dynamic COUNT using INTO
    EXECUTE IMMEDIATE v_sql_stmt INTO v_row_count;

    DBMS_OUTPUT.PUT_LINE('Total rows in ' || v_table_name || ': ' || v_row_count);

    -- Dynamic COUNT with WHERE clause
    v_sql_stmt := 'SELECT COUNT(*) FROM ' || v_table_name || ' WHERE salary > :min_salary';
    DECLARE
        v_min_salary NUMBER := 60000;
        v_filtered_count NUMBER;
    BEGIN
        EXECUTE IMMEDIATE v_sql_stmt INTO v_filtered_count USING v_min_salary;
        DBMS_OUTPUT.PUT_LINE('Rows in ' || v_table_name || ' with salary > ' || v_min_salary || ': ' || v_filtered_count);
    END;

EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error counting rows: ' || SQLERRM);
END;
/

Explanation

This example shows how to dynamically count rows in a table. The v_sql_stmt is built to perform a COUNT(*) query. The INTO v_row_count then captures the single numerical result. This pattern is very common for dynamic reporting or data validation. We also show how to add a dynamic WHERE clause with a bind variable for more specific counts.

The BULK COLLECT INTO Clause for Multiple Rows

When your dynamic SQL statement is a SELECT query that is expected to return multiple rows, you should use the BULK COLLECT INTO clause. This technique significantly improves performance by fetching multiple rows at once (in batches) instead of row-by-row processing, which reduces the number of context switches between the SQL and PL/SQL engines. It's ideal for populating collections (nested tables or varrays).

Example 1: Fetching into a Simple Collection of Scalars

This example demonstrates fetching a list of names into a collection of VARCHAR2.

DECLARE
    TYPE NameArray IS TABLE OF VARCHAR2(100);
    v_employee_names NameArray;
    v_department_id  NUMBER := 10;
    v_sql_stmt       VARCHAR2(300);
BEGIN
    -- Assume EMPLOYEES table with employee_name and department_id
    -- For demonstration, insert sample records
    BEGIN
        EXECUTE IMMEDIATE 'CREATE TABLE EMPLOYEES (employee_id NUMBER PRIMARY KEY, employee_name VARCHAR2(100), department_id NUMBER)';
    EXCEPTION
        WHEN OTHERS THEN NULL;
    END;
    BEGIN
        EXECUTE IMMEDIATE 'INSERT INTO EMPLOYEES (employee_id, employee_name, department_id) VALUES (1, ''Alice'', 10)';
        EXECUTE IMMEDIATE 'INSERT INTO EMPLOYEES (employee_id, employee_name, department_id) VALUES (2, ''Bob'', 20)';
        EXECUTE IMMEDIATE 'INSERT INTO EMPLOYEES (employee_id, employee_name, department_id) VALUES (3, ''Charlie'', 10)';
        EXECUTE IMMEDIATE 'INSERT INTO EMPLOYEES (employee_id, employee_name, department_id) VALUES (4, ''David'', 30)';
    EXCEPTION
        WHEN DUP_VAL_ON_INDEX THEN NULL;
    END;
    COMMIT;

    -- Construct the dynamic SELECT statement with BULK COLLECT
    v_sql_stmt := 'SELECT employee_name FROM EMPLOYEES WHERE department_id = :dept_id ORDER BY employee_name';

    -- Execute the dynamic SELECT using BULK COLLECT INTO
    EXECUTE IMMEDIATE v_sql_stmt BULK COLLECT INTO v_employee_names USING v_department_id;

    DBMS_OUTPUT.PUT_LINE('Employees in Department ' || v_department_id || ':');
    IF v_employee_names.COUNT > 0 THEN
        FOR i IN 1..v_employee_names.COUNT LOOP
            DBMS_OUTPUT.PUT_LINE('- ' || v_employee_names(i));
        END LOOP;
    ELSE
        DBMS_OUTPUT.PUT_LINE('No employees found in department ' || v_department_id);
    END IF;

EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error fetching employee names: ' || SQLERRM);
END;
/

Explanation

First, we define a nested table type NameArray. The v_sql_stmt is a standard SELECT query. The magic happens with BULK COLLECT INTO v_employee_names. This tells Oracle to fetch all rows returned by the dynamic query and populate them directly into the v_employee_names collection. The USING clause passes the department ID as a bind variable.

Example 2: Fetching into a Collection of Records

When you need to fetch multiple columns for multiple rows, using a collection of record types is highly efficient and organized.

DECLARE
    TYPE EmployeeInfoRec IS RECORD (
        employee_name VARCHAR2(100),
        department_id NUMBER
    );
    TYPE EmployeeInfoArray IS TABLE OF EmployeeInfoRec;
    v_employee_list EmployeeInfoArray;
    v_min_salary    NUMBER := 10000;
    v_sql_stmt      VARCHAR2(300);
BEGIN
    -- Assume EMPLOYEES table (from previous example) with salary column
    BEGIN
        EXECUTE IMMEDIATE 'ALTER TABLE EMPLOYEES ADD salary NUMBER';
    EXCEPTION
        WHEN OTHERS THEN NULL; -- Column likely exists
    END;
    EXECUTE IMMEDIATE 'UPDATE EMPLOYEES SET salary = 50000 WHERE employee_id = 1';
    EXECUTE IMMEDIATE 'UPDATE EMPLOYEES SET salary = 70000 WHERE employee_id = 2';
    EXECUTE IMMEDIATE 'UPDATE EMPLOYEES SET salary = 60000 WHERE employee_id = 3';
    EXECUTE IMMEDIATE 'UPDATE EMPLOYEES SET salary = 40000 WHERE employee_id = 4';
    COMMIT;

    -- Construct the dynamic SELECT statement for multiple columns into a record
    v_sql_stmt := 'SELECT employee_name, department_id FROM EMPLOYEES WHERE salary > :sal ORDER BY employee_name';

    -- Execute the dynamic SELECT using BULK COLLECT INTO a collection of records
    EXECUTE IMMEDIATE v_sql_stmt BULK COLLECT INTO v_employee_list USING v_min_salary;

    DBMS_OUTPUT.PUT_LINE('Employees with salary > ' || v_min_salary || ':');
    IF v_employee_list.COUNT > 0 THEN
        FOR i IN 1..v_employee_list.COUNT LOOP
            DBMS_OUTPUT.PUT_LINE('- Name: ' || v_employee_list(i).employee_name || ', Dept ID: ' || v_employee_list(i).department_id);
        END LOOP;
    ELSE
        DBMS_OUTPUT.PUT_LINE('No employees found with salary > ' || v_min_salary);
    END IF;

EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error fetching employee info: ' || SQLERRM);
END;
/

Explanation

Here, we define a record type EmployeeInfoRec and a collection of that record type EmployeeInfoArray. The dynamic SELECT fetches employee_name and department_id. BULK COLLECT INTO v_employee_list populates the collection, with each element being a record holding the corresponding name and department ID. This is a very clean way to handle multi-column, multi-row results.

Example 3: Dynamic WHERE Clause with BULK COLLECT

This example shows how to build a flexible WHERE clause dynamically and still use BULK COLLECT.

DECLARE
    TYPE ProductNames IS TABLE OF VARCHAR2(100);
    v_product_list ProductNames;
    v_category     VARCHAR2(50) := 'Electronics';
    v_min_price    NUMBER       := 500;
    v_sql_stmt     VARCHAR2(500);
    v_where_clause VARCHAR2(200);
BEGIN
    -- Assume PRODUCTS table with category and price
    BEGIN
        EXECUTE IMMEDIATE 'ALTER TABLE PRODUCTS ADD category VARCHAR2(50)';
    EXCEPTION
        WHEN OTHERS THEN NULL;
    END;
    EXECUTE IMMEDIATE 'UPDATE PRODUCTS SET category = ''Electronics'' WHERE product_id = 1';
    EXECUTE IMMEDIATE 'UPDATE PRODUCTS SET category = ''Office'' WHERE product_id = 2';
    EXECUTE IMMEDIATE 'INSERT INTO PRODUCTS (product_id, product_name, price, category) VALUES (3, ''Monitor'', 300.00, ''Electronics'')';
    INSERT INTO PRODUCTS (product_id, product_name, price, category) VALUES (4, 'Keyboard', 75.00, 'Electronics');
    INSERT INTO PRODUCTS (product_id, product_name, price, category) VALUES (5, 'Desk', 200.00, 'Office');
    COMMIT;

    -- Construct the dynamic WHERE clause based on conditions
    v_where_clause := 'WHERE 1 = 1'; -- Start with a true condition to easily append AND clauses
    IF v_category IS NOT NULL THEN
        v_where_clause := v_where_clause || ' AND category = :cat';
    END IF;
    IF v_min_price IS NOT NULL THEN
        v_where_clause := v_where_clause || ' AND price >= :price';
    END IF;

    -- Construct the full dynamic SQL statement
    v_sql_stmt := 'SELECT product_name FROM PRODUCTS ' || v_where_clause || ' ORDER BY product_name';

    -- Execute the dynamic SELECT using BULK COLLECT and conditionally add USING clause variables
    IF v_category IS NOT NULL AND v_min_price IS NOT NULL THEN
        EXECUTE IMMEDIATE v_sql_stmt BULK COLLECT INTO v_product_list USING v_category, v_min_price;
    ELSIF v_category IS NOT NULL THEN
        EXECUTE IMMEDIATE v_sql_stmt BULK COLLECT INTO v_product_list USING v_category;
    ELSIF v_min_price IS NOT NULL THEN
        EXECUTE IMMEDIATE v_sql_stmt BULK COLLECT INTO v_product_list USING v_min_price;
    ELSE
        EXECUTE IMMEDIATE v_sql_stmt BULK COLLECT INTO v_product_list;
    END IF;

    DBMS_OUTPUT.PUT_LINE('Products matching criteria:');
    IF v_product_list.COUNT > 0 THEN
        FOR i IN 1..v_product_list.COUNT LOOP
            DBMS_OUTPUT.PUT_LINE('- ' || v_product_list(i));
        END LOOP;
    ELSE
        DBMS_OUTPUT.PUT_LINE('No products found matching the criteria.');
    END IF;

EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error fetching products with dynamic WHERE: ' || SQLERRM);
END;
/
/

Explanation

This is a more advanced example where the WHERE clause itself is built dynamically based on whether v_category and v_min_price have values. The USING clause is then conditionally constructed based on which bind variables are actually needed. This demonstrates how flexible dynamic SQL can be when combined with conditional logic.

Example 4: Handling Empty Result Sets with BULK COLLECT

Unlike INTO, BULK COLLECT INTO does not raise a NO_DATA_FOUND exception if the query returns zero rows. The collection will simply be empty.

DECLARE
    TYPE EmployeeIDs IS TABLE OF NUMBER;
    v_employee_ids EmployeeIDs;
    v_non_existent_dept_id NUMBER := 99;
    v_sql_stmt             VARCHAR2(200);
BEGIN
    -- Construct the dynamic SELECT statement
    v_sql_stmt := 'SELECT employee_id FROM EMPLOYEES WHERE department_id = :dept_id';

    -- Execute the dynamic SELECT for a non-existent department
    EXECUTE IMMEDIATE v_sql_stmt BULK COLLECT INTO v_employee_ids USING v_non_existent_dept_id;

    DBMS_OUTPUT.PUT_LINE('Employees in Department ' || v_non_existent_dept_id || ':');
    IF v_employee_ids.COUNT > 0 THEN
        FOR i IN 1..v_employee_ids.COUNT LOOP
            DBMS_OUTPUT.PUT_LINE('- Employee ID: ' || v_employee_ids(i));
        END LOOP;
    ELSE
        DBMS_OUTPUT.PUT_LINE('No employees found in department ' || v_non_existent_dept_id || '. (Expected behavior, no NO_DATA_FOUND)');
    END IF;

EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/

Explanation

This example highlights a key difference: BULK COLLECT INTO gracefully handles queries that return no rows by simply returning an empty collection. You should check collection.COUNT > 0 to determine if any rows were fetched. This is safer than INTO for scenarios where zero rows are a valid outcome.

Example 5: Dynamic ORDER BY and BULK COLLECT

You can dynamically change the ORDER BY clause of your SELECT statement, making your reports or data displays highly customizable.

DECLARE
    TYPE EmployeeNames IS TABLE OF VARCHAR2(100);
    v_employee_names EmployeeNames;
    v_order_by_column VARCHAR2(50) := 'employee_name'; -- Can be 'employee_id' or 'salary'
    v_order_direction VARCHAR2(10) := 'ASC';             -- Can be 'DESC'
    v_sql_stmt        VARCHAR2(300);
BEGIN
    -- Ensure all columns are available for order by
    BEGIN
        EXECUTE IMMEDIATE 'ALTER TABLE EMPLOYEES ADD (employee_id NUMBER, salary NUMBER)';
    EXCEPTION
        WHEN OTHERS THEN NULL;
    END;
    -- Just to be safe for existing data, update some values
    EXECUTE IMMEDIATE 'UPDATE EMPLOYEES SET employee_id = 1, salary = 50000 WHERE employee_name = ''Alice''';
    EXECUTE IMMEDIATE 'UPDATE EMPLOYEES SET employee_id = 2, salary = 70000 WHERE employee_name = ''Bob''';
    EXECUTE IMMEDIATE 'UPDATE EMPLOYEES SET employee_id = 3, salary = 60000 WHERE employee_name = ''Charlie''';
    EXECUTE IMMEDIATE 'UPDATE EMPLOYEES SET employee_id = 4, salary = 40000 WHERE employee_name = ''David''';
    COMMIT;


    -- Construct the dynamic SELECT statement with dynamic ORDER BY
    v_sql_stmt := 'SELECT employee_name FROM EMPLOYEES ORDER BY ' || v_order_by_column || ' ' || v_order_direction;

    -- Execute the dynamic SELECT using BULK COLLECT
    EXECUTE IMMEDIATE v_sql_stmt BULK COLLECT INTO v_employee_names;

    DBMS_OUTPUT.PUT_LINE('Employees sorted by ' || v_order_by_column || ' ' || v_order_direction || ':');
    IF v_employee_names.COUNT > 0 THEN
        FOR i IN 1..v_employee_names.COUNT LOOP
            DBMS_OUTPUT.PUT_LINE('- ' || v_employee_names(i));
        END LOOP;
    ELSE
        DBMS_OUTPUT.PUT_LINE('No employees found.');
    END IF;

    -- Change order by to salary descending
    v_order_by_column := 'salary';
    v_order_direction := 'DESC';
    v_sql_stmt := 'SELECT employee_name FROM EMPLOYEES ORDER BY ' || v_order_by_column || ' ' || v_order_direction;
    EXECUTE IMMEDIATE v_sql_stmt BULK COLLECT INTO v_employee_names;
    DBMS_OUTPUT.PUT_LINE('Employees sorted by ' || v_order_by_column || ' ' || v_order_direction || ':');
    IF v_employee_names.COUNT > 0 THEN
        FOR i IN 1..v_employee_names.COUNT LOOP
            DBMS_OUTPUT.PUT_LINE('- ' || v_employee_names(i));
        END LOOP;
    ELSE
        DBMS_OUTPUT.PUT_LINE('No employees found.');
    END IF;


EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error fetching employees with dynamic ORDER BY: ' || SQLERRM);
END;
/

Explanation

This example demonstrates a powerful use case: allowing users to specify the sorting criteria for a report. The ORDER BY clause is constructed using PL/SQL variables, and then the full query is executed with BULK COLLECT. This provides immense flexibility without requiring a separate, hardcoded query for each sort order.

The DBMS_SQL Package

The DBMS_SQL package provides a more powerful and flexible (but also more complex) interface for building and executing dynamic SQL compared to Native Dynamic SQL (EXECUTE IMMEDIATE). It allows you to parse, bind, and execute SQL statements step-by-step, offering fine-grained control, especially for queries where the number or data types of bind variables or select-list items are not known until runtime. It's often referred to as "Method 4" dynamic SQL, while NDS is "Method 3".

When to use DBMS_SQL

You should consider using the DBMS_SQL package in the following scenarios:

Unknown Number/Types of Bind Variables: When the number of bind variables or their data types in your SQL statement are not known until runtime. For example, building a search query where users can select an arbitrary number of filter criteria.

Unknown Number/Types of Select-List Items: When the columns you need to retrieve from a SELECT statement are not fixed and can vary at runtime. This is common in generic reporting tools.

Highly Complex Dynamic SQL: For extremely intricate dynamic SQL generation where EXECUTE IMMEDIATE might become unwieldy due to a large number of conditional clauses or variable types.

Backward Compatibility: In older Oracle versions where NDS was not fully featured or available. However, for modern Oracle versions, NDS covers most dynamic SQL needs more simply.

Advanced Features: DBMS_SQL offers some advanced features like describing columns, fetching values by position, and array binding (though NDS also supports array binding for DML).

Example 1: Basic DBMS_SQL for a Fixed Query

While EXECUTE IMMEDIATE is simpler for this, this example illustrates the basic steps of DBMS_SQL for a fixed query.

DECLARE
    v_cursor_id   INTEGER;
    v_rows_parsed NUMBER;
    v_employee_id NUMBER := 101;
    v_employee_name VARCHAR2(100);
    v_sql_stmt    VARCHAR2(200) := 'SELECT employee_name FROM EMPLOYEES WHERE employee_id = :id';
BEGIN
    -- 1. Open a new cursor
    v_cursor_id := DBMS_SQL.OPEN_CURSOR;

    -- 2. Parse the SQL statement
    DBMS_SQL.PARSE(v_cursor_id, v_sql_stmt, DBMS_SQL.NATIVE);

    -- 3. Bind variables (if any)
    DBMS_SQL.BIND_VARIABLE(v_cursor_id, ':id', v_employee_id);

    -- 4. Define output columns
    DBMS_SQL.DEFINE_COLUMN(v_cursor_id, 1, v_employee_name, 100); -- Position 1, variable, max length

    -- 5. Execute the cursor
    v_rows_parsed := DBMS_SQL.EXECUTE(v_cursor_id);

    -- 6. Fetch results (for SELECT statements)
    IF DBMS_SQL.FETCH_ROWS(v_cursor_id) > 0 THEN
        -- 7. Get column values
        DBMS_SQL.COLUMN_VALUE(v_cursor_id, 1, v_employee_name);
        DBMS_OUTPUT.PUT_LINE('Employee Name (DBMS_SQL): ' || v_employee_name);
    ELSE
        DBMS_OUTPUT.PUT_LINE('No employee found with ID: ' || v_employee_id);
    END IF;

EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error with DBMS_SQL: ' || SQLERRM);
        IF DBMS_SQL.IS_OPEN(v_cursor_id) THEN
            DBMS_SQL.CLOSE_CURSOR(v_cursor_id);
        END IF;
END;
/

Explanation

This example breaks down the DBMS_SQL process:

OPEN_CURSOR: Obtains a unique cursor ID.

PARSE: Parses the SQL string. DBMS_SQL.NATIVE indicates the native SQL parser.

BIND_VARIABLE: Associates the PL/SQL variable v_employee_id with the bind placeholder :id.

DEFINE_COLUMN: Specifies the data type and maximum length for each column in the select list. This is crucial for DBMS_SQL to know how to retrieve the data.

EXECUTE: Executes the parsed statement. For SELECT statements, it prepares the result set.

FETCH_ROWS: Fetches a row from the result set.

COLUMN_VALUE: Retrieves the value of a specific column from the fetched row into a PL/SQL variable. Finally, CLOSE_CURSOR is essential to release resources. Error handling also closes the cursor.

Example 2: Dynamic DDL with DBMS_SQL

You can also execute DDL statements using DBMS_SQL.

DECLARE
    v_cursor_id INTEGER;
    v_table_name VARCHAR2(100) := 'NEW_LOG_TABLE_' || TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS');
    v_sql_stmt   VARCHAR2(500);
BEGIN
    v_cursor_id := DBMS_SQL.OPEN_CURSOR;

    v_sql_stmt := 'CREATE TABLE ' || v_table_name || ' ( ' ||
                  '  log_id NUMBER PRIMARY KEY, ' ||
                  '  log_message VARCHAR2(255), ' ||
                  '  log_date DATE DEFAULT SYSDATE ' ||
                  ')';

    DBMS_SQL.PARSE(v_cursor_id, v_sql_stmt, DBMS_SQL.NATIVE);

    -- For DDL, EXECUTE returns the number of rows processed, which is typically 0
    IF DBMS_SQL.EXECUTE(v_cursor_id) = 0 THEN
        DBMS_OUTPUT.PUT_LINE('Table ' || v_table_name || ' created successfully using DBMS_SQL.');
    ELSE
        DBMS_OUTPUT.PUT_LINE('DDL execution completed with non-zero result. Table ' || v_table_name || ' might have been created.');
    END IF;

    DBMS_SQL.CLOSE_CURSOR(v_cursor_id);

EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error creating table with DBMS_SQL: ' || SQLERRM);
        IF DBMS_SQL.IS_OPEN(v_cursor_id) THEN
            DBMS_SQL.CLOSE_CURSOR(v_cursor_id);
        END IF;
END;
/

Explanation

This example is similar to a DDL example with EXECUTE IMMEDIATE, but it uses the explicit DBMS_SQL steps. DBMS_SQL.EXECUTE is called, and for DDL, it typically returns 0 as no rows are directly affected in the same way as DML.

Example 3: Describing Columns for Unknown Select Lists

This is where DBMS_SQL truly shines: when you don't know the columns being selected until runtime.

DECLARE
    v_cursor_id   INTEGER;
    v_sql_stmt    VARCHAR2(500) := 'SELECT product_id, product_name, price FROM PRODUCTS WHERE ROWNUM <= 2';
    v_col_cnt     INTEGER;
    v_rec_tab     DBMS_SQL.DESC_TAB;
    v_name        VARCHAR2(256);
    v_num         NUMBER;
    v_date        DATE;
BEGIN
    v_cursor_id := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(v_cursor_id, v_sql_stmt, DBMS_SQL.NATIVE);

    -- Describe the columns: get metadata about the select list
    DBMS_SQL.DESCRIBE_COLUMNS(v_cursor_id, v_col_cnt, v_rec_tab);

    -- Define output columns based on description
    FOR i IN 1..v_col_cnt LOOP
        CASE v_rec_tab(i).col_type
            WHEN 1 THEN -- VARCHAR2
                DBMS_SQL.DEFINE_COLUMN(v_cursor_id, i, v_name, 256);
            WHEN 2 THEN -- NUMBER
                DBMS_SQL.DEFINE_COLUMN(v_cursor_id, i, v_num);
            WHEN 12 THEN -- DATE
                DBMS_SQL.DEFINE_COLUMN(v_cursor_id, i, v_date);
            ELSE
                DBMS_SQL.DEFINE_COLUMN(v_cursor_id, i, v_name, 256); -- Default to VARCHAR2 for others
        END CASE;
    END LOOP;

    -- Execute and fetch
    v_num := DBMS_SQL.EXECUTE(v_cursor_id);

    DBMS_OUTPUT.PUT_LINE('Fetched Rows:');
    WHILE DBMS_SQL.FETCH_ROWS(v_cursor_id) > 0 LOOP
        DBMS_OUTPUT.PUT_LINE('--- Row ---');
        FOR i IN 1..v_col_cnt LOOP
            DBMS_OUTPUT.PUT_LINE('Column ' || i || ' Name: ' || v_rec_tab(i).col_name);
            CASE v_rec_tab(i).col_type
                WHEN 1 THEN
                    DBMS_SQL.COLUMN_VALUE(v_cursor_id, i, v_name);
                    DBMS_OUTPUT.PUT_LINE('  Value (VARCHAR2): ' || v_name);
                WHEN 2 THEN
                    DBMS_SQL.COLUMN_VALUE(v_cursor_id, i, v_num);
                    DBMS_OUTPUT.PUT_LINE('  Value (NUMBER): ' || v_num);
                WHEN 12 THEN
                    DBMS_SQL.COLUMN_VALUE(v_cursor_id, i, v_date);
                    DBMS_OUTPUT.PUT_LINE('  Value (DATE): ' || TO_CHAR(v_date, 'YYYY-MM-DD HH24:MI:SS'));
            END CASE;
        END LOOP;
    END LOOP;

    DBMS_SQL.CLOSE_CURSOR(v_cursor_id);

EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error describing columns with DBMS_SQL: ' || SQLERRM);
        IF DBMS_SQL.IS_OPEN(v_cursor_id) THEN
            DBMS_SQL.CLOSE_CURSOR(v_cursor_id);
        END IF;
END;
/

Explanation

This is a more complex but powerful DBMS_SQL example. After parsing, DBMS_SQL.DESCRIBE_COLUMNS retrieves metadata about each column in the select list (like name, type, length). We then loop through this metadata to dynamically call DEFINE_COLUMN with the appropriate type. During fetching, we again use the metadata to call COLUMN_VALUE with the correct target variable type. This is essential for building generic query tools where the shape of the result set is not known at compile time.

Example 4: Dynamic Number of Bind Variables with DBMS_SQL

When the number of bind variables can change at runtime, DBMS_SQL provides explicit control.

DECLARE
    v_cursor_id INTEGER;
    v_sql_stmt  VARCHAR2(500);
    v_where_clause VARCHAR2(200);
    v_bind_val_1 VARCHAR2(100);
    v_bind_val_2 NUMBER;
    v_product_name VARCHAR2(100);
BEGIN
    -- Scenario: Build a WHERE clause with optional filters
    v_sql_stmt := 'SELECT product_name FROM PRODUCTS';
    v_where_clause := '';

    -- Conditionally add filters and bind variables
    IF TRUE THEN -- Simulate a condition where category is filtered
        v_where_clause := v_where_clause || ' WHERE category = :cat';
        v_bind_val_1 := 'Electronics';
    END IF;

    IF TRUE THEN -- Simulate a condition where price is filtered
        IF v_where_clause IS NULL THEN
            v_where_clause := ' WHERE price >= :price';
        ELSE
            v_where_clause := v_where_clause || ' AND price >= :price';
        END IF;
        v_bind_val_2 := 100;
    END IF;

    v_sql_stmt := v_sql_stmt || v_where_clause || ' ORDER BY product_name';

    DBMS_OUTPUT.PUT_LINE('Executing SQL: ' || v_sql_stmt);

    v_cursor_id := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(v_cursor_id, v_sql_stmt, DBMS_SQL.NATIVE);

    -- Bind variables based on conditions
    IF v_bind_val_1 IS NOT NULL THEN
        DBMS_SQL.BIND_VARIABLE(v_cursor_id, ':cat', v_bind_val_1);
    END IF;
    IF v_bind_val_2 IS NOT NULL THEN
        DBMS_SQL.BIND_VARIABLE(v_cursor_id, ':price', v_bind_val_2);
    END IF;

    DBMS_SQL.DEFINE_COLUMN(v_cursor_id, 1, v_product_name, 100);

    IF DBMS_SQL.EXECUTE_AND_FETCH(v_cursor_id, TRUE) > 0 THEN -- Execute and fetch one row
        DBMS_SQL.COLUMN_VALUE(v_cursor_id, 1, v_product_name);
        DBMS_OUTPUT.PUT_LINE('First matching product: ' || v_product_name);
    ELSE
        DBMS_OUTPUT.PUT_LINE('No products found matching criteria.');
    END IF;

    DBMS_SQL.CLOSE_CURSOR(v_cursor_id);

EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error with dynamic bind variables in DBMS_SQL: ' || SQLERRM);
        IF DBMS_SQL.IS_OPEN(v_cursor_id) THEN
            DBMS_SQL.CLOSE_CURSOR(v_cursor_id);
        END IF;
END;
/

Explanation

This example builds a WHERE clause dynamically, and critically, it conditionally binds variables based on which parts of the WHERE clause are included. This fine-grained control over binding is a key reason to use DBMS_SQL for truly dynamic queries where the parameters are unknown at compile time.

Example 5: Fetching Multiple Rows with DBMS_SQL (Looping)

While BULK COLLECT with NDS is often preferred, DBMS_SQL can also fetch multiple rows in a loop.

DECLARE
    v_cursor_id   INTEGER;
    v_sql_stmt    VARCHAR2(200) := 'SELECT employee_name, department_id FROM EMPLOYEES ORDER BY employee_name';
    v_employee_name VARCHAR2(100);
    v_department_id NUMBER;
BEGIN
    v_cursor_id := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(v_cursor_id, v_sql_stmt, DBMS_SQL.NATIVE);

    -- Define output columns
    DBMS_SQL.DEFINE_COLUMN(v_cursor_id, 1, v_employee_name, 100);
    DBMS_SQL.DEFINE_COLUMN(v_cursor_id, 2, v_department_id);

    v_department_id := DBMS_SQL.EXECUTE(v_cursor_id); -- For SELECT, this returns a dummy value, not rows parsed

    DBMS_OUTPUT.PUT_LINE('All Employees:');
    WHILE DBMS_SQL.FETCH_ROWS(v_cursor_id) > 0 LOOP
        DBMS_SQL.COLUMN_VALUE(v_cursor_id, 1, v_employee_name);
        DBMS_SQL.COLUMN_VALUE(v_cursor_id, 2, v_department_id);
        DBMS_OUTPUT.PUT_LINE('- Name: ' || v_employee_name || ', Dept: ' || v_department_id);
    END LOOP;

    DBMS_SQL.CLOSE_CURSOR(v_cursor_id);

EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error fetching multiple rows with DBMS_SQL: ' || SQLERRM);
        IF DBMS_SQL.IS_OPEN(v_cursor_id) THEN
            DBMS_SQL.CLOSE_CURSOR(v_cursor_id);
        END IF;
END;
/

Explanation

This example demonstrates a classic DBMS_SQL loop for fetching multiple rows. After executing the cursor, the WHILE DBMS_SQL.FETCH_ROWS(v_cursor_id) > 0 loop continues to fetch rows one by one. Inside the loop, DBMS_SQL.COLUMN_VALUE retrieves the values for each column into the defined PL/SQL variables.

A comparison of NDS and DBMS_SQL

Both Native Dynamic SQL (EXECUTE IMMEDIATE) and the DBMS_SQL package allow you to execute dynamic SQL, but they differ significantly in their approach, complexity, and capabilities. Understanding these differences is key to choosing the right tool for your dynamic SQL needs.

Key Takeaways:

For 90-95% of dynamic SQL needs, Native Dynamic SQL (EXECUTE IMMEDIATE) is the preferred choice due to its simplicity, readability, and performance.

DBMS_SQL is a powerful, low-level API that provides greater control and flexibility, especially when you need to dynamically discover the structure of a SQL statement (e.g., column names, data types, or the number of bind variables) at runtime.

Always use bind variables regardless of whether you choose NDS or DBMS_SQL to protect against SQL injection and improve performance.

By mastering both EXECUTE IMMEDIATE and DBMS_SQL, you'll be well-equipped to handle any dynamic SQL challenge in your Oracle PL/SQL development.