Dynamic SQL

Dynamic SQL refers to the practice of executing SQL statements dynamically at runtime, rather than using static SQL queries defined at program compilation. More specifically, it involves storing SQL clauses as character strings and using them to dynamically construct SQL statements that will be executed during program execution.

One common usage of dynamic SQL is constructing the WHERE clause for search conditions. For example, consider a search filter with 10 different criteria. Typically, end-users don't apply all 10 filters at once. The challenge here is that there are \(2^{10}\) possible combinations of these filters (as each condition can either be included or excluded), resulting in 1024 different potential query variations. Writing static SQL queries for every combination would be impractical. Instead, dynamic SQL allows the application to construct the WHERE clause on the fly based on the user's selected conditions, making the process more efficient and scalable.

Here are some additional use cases for dynamic SQL:

• Dynamic Column Selection:
• Not only can the WHERE clause of an SQL query be undetermined at compile time, but the SELECT clause can also require dynamic construction. In many scenarios, the specific columns to be retrieved from a database may depend on user input or application logic that is only known at runtime.
• Executing DDL on PL/SQL Block:
• In a PL/SQL block, you cannot directly execute DDL statements, such as CREATE, DROP, ALTER, or TRUNCATE. Although it is less common to use DDL statements within PL/SQL, the TRUNCATE command can still be useful in certain scenarios. Dynamic SQL provides a workaround for the restrictions on DDL statements in PL/SQL code blocks, allowing you to construct and execute these commands at runtime.
• Running ALTER SYSTEM/SESSION Commands from PL/SQL Block:
• Oracle has various system and session parameters, such as NLS_LANG or NLS_DATE_FORMAT. These parameters can be set at the system level or for individual sessions using the ALTER SESSION statement. However, similar to DDL statements, the ALTER SESSION statement cannot be executed directly within a PL/SQL block. Dynamic SQL allows you to adjust session-specific settings based on runtime requirements. 

Native Dynamic SQL 

One approach for dynamic SQL is using native dynamic SQL (NDS), which was introduced to simplify the complex syntax of dbms_sql system package. Particularly, starting from Oracle version 11g, the previously imposed 32KB (32767 bytes) string limit on NDS was relaxed, allowing support for the CLOB data type, which can store up to 4GB of strings. As a result, we can practically say that the length limit for dynamic SQL statements using NDS has been eliminated, ensuring that developers will rarely, if ever, find 4GB to be insufficient for their needs. 

EXECUTE IMMEDIATE Statement

The most basic dynamic SQL is the EXECUTE IMMEDIATE statement, which has the following syntax:

EXECUTE IMMEDIATE 'sql-string'
[INTO output_variable_1, output_variable_2, ...]
[Using [IN | OUT | IN OUT] parameter_1,
       [IN | OUT | IN OUT] parameter_2, ...]
;

The syntax is straightforward; place SQL command parts enclosed in single quotes after the keyword EXECUTE IMMEDIATE. The optional INTO clause is where you can list PL/SQL variables to store SELECT query outputs. The USING clause is used to bind values to placeholders in the SQL string, allowing for the dynamic substitution of parameters. 

Dynamic SELECT Statements 

Let's first write a PL/SQL code block with a simple dynamic SELECT statement. 

In this PL/SQL block, the SQL query is constructed as a string and assigned to the v_query variable. Being a PL/SQL variable, the string can be dynamically modified based on the program's logic or condition, allowing for flexible query construction in a procedural manner. Once the query string is ready, the EXECUTE IMMEDIATE statement is used to execute the SQL query. The INTO clause then stores the query results into PL/SQL variables for further data processing.

To include single quotes within the query string, they must be escaped by doubling them, as seen with the product name ''Boy''''s Shirt (White)''. The first and last two single quotes are used to delimit the entire string, while the four quotes inside the string represent a single quote that appears in the product name itself. 

Let's explore another example using bind variables. In the declaration section of the block shown below, three PL/SQL variables (v_lower_bound, v_upper_bound, v_customer_id) are declared. Then, in the executable section, the SQL query is constructed using three placeholders (:a, :b, :c). These placeholders will be replaced by the PL/SQL variables which contains the actual values when the query is executed. The USING clause plays an important role as it binds the actual values to the placeholders in the query. Here, v_lower_bound, v_upper_bound, v_customer_id are passed as arguments corresponding to :a, :b, and :c, respectively. 

As seen in this example, the bind variables should be prefixed with a colon (:) to denote that they are placeholders. It is important to note that the order of the bind variables and the data types in the USING clause must match the order of the placeholders in the SQL statement. The specific name of the bind variable generally does not carry any significance and even can be duplicated within the same scope; what matters is the position of the bind variable in the SQL statement and its corresponding entry in the USING clause. For example, we can rewrite the previous PL/SQL block as follows:

Observe that the first :a is bound to v_lower_bound, whereas the second :a is bound to v_upper_bound. Even if placeholder names are duplicated, you must still provide the corresponding values in the correct order for each occurrence to ensure the appropriate variables is assigned to each placeholder.

When you submit an SQL query, Oracle first checks its syntax and generates execution plans. It then executes the query using the most efficient execution plan. However, the process of generating execution plans and selecting the optimal one demands significant computational resources. Thus, if Oracle identifies a previous execution of the same query in memory, it will execute the query based on that cached execution plan, reducing resource usage. 

Here, if the WHERE clause conditions are specified through literal values, Oracle will treat the resubmitted query as different from the previous. As a result, it will repeat the execution plan optimization process each time the query is submitted. To avoid this overhead, you should specify the conditions through bind variables. The bind variables are placeholders in SQL statements that allow you to pass values at runtime, rather than hardcoding them directly into the query. By using bind variables, you enable Oracle to recognize the underlying structure of the query as the same, even when the specified values change. This allows Oracle to reuse cached execution plans, improving performance and reducing resource consumptions. 

Dynamic DML

Dynamic SQL is not limited to the SELECT statement: it can also be used for INSERT, UPDATE, DELETE, and MERGE statement. For example, let's consider a database table created as follows:

Suppose you want to insert a row into this table from a PL/SQL block. Using NDS, you can achieve this as follows:

The use of the NDS and bind variables is the same for the other DML statements, such as DELETE, MERGE, and/or UPDATE. For example:

OPEN FOR Statement

d

DDL and ALTER SESSION

DBMS_SQL System Package

Another approach for dynamic SQL is using dbms_sql, which is an Oracle supplied package. This package contains functions, procedures, and user-defined data types for dynamic SQL. In fact, the dbms_sql package is a