PL/SQL EXECUTE IMMEDIATE

In PL/SQL, the EXECUTE IMMEDIATE statement is a powerful and flexible feature that allows you to dynamically execute SQL statements or PL/SQL blocks. This statement is particularly useful when you need to construct SQL statements dynamically at runtime, based on certain conditions or user inputs.

Syntax

The basic syntax of the EXECUTE IMMEDIATE statement is as follows:

EXECUTE IMMEDIATE dynamic_sql_string [INTO {bind_variable [, bind_variable]... | record}];

Here, dynamic_sql_string is a string expression containing the SQL statement or PL/SQL block that you want to execute dynamically. You can include placeholders for bind variables in the dynamic SQL string, which will be replaced by actual values at runtime.

The optional INTO clause is used to retrieve the result of a query into specified variables or a record. If the dynamic SQL statement returns a single value or a single row, you can use the INTO clause to capture the result.

Example

Here’s a simple example of using the EXECUTE IMMEDIATE statement to execute a dynamic SQL statement:

DECLARE
sql_string VARCHAR2(100);
result_value NUMBER;
BEGIN
sql_string := 'SELECT COUNT(*) FROM employees WHERE department_id = :dept_id';
EXECUTE IMMEDIATE sql_string INTO result_value USING 20;
DBMS_OUTPUT.PUT_LINE('Number of employees in department 20: ' || result_value);
END;

In this example, the sql_string variable holds a dynamic SQL statement that counts the number of employees in a specified department. The USING clause is used to pass a bind variable value (department_id = 20) to the dynamic SQL statement. The result is then stored in the result_value variable.

It’s important to note that using dynamic SQL with EXECUTE IMMEDIATE can introduce security risks such as SQL injection if not handled carefully. It’s recommended to validate and sanitize user inputs and use bind variables to mitigate these risks.

In summary, the EXECUTE IMMEDIATE statement in PL/SQL provides a mechanism for executing dynamically generated SQL statements or PL/SQL blocks, offering flexibility in dealing with varying runtime scenarios and conditions.