Document Type | Technical Information
Category | Administration
Applicable Product Version | 7FS02PS
Document Number | TADTI046
Overview
Dynamic SQL refers to SQL statements that are generated and executed at runtime.
When the query statement is fixed, it is called static SQL; when the query statement changes each time it is executed, it is classified as dynamic SQL.
Most often, when using dynamic SQL, the query is stored as a string and changed each time it is executed by modifying the text of the query statement.
Since most applications use queries constructed as strings and pass them at runtime, the dynamic SQL method is used.
Situations where dynamic SQL is necessary are as follows.
1. When the SQL statement cannot be determined at compile time
The most frequent use of dynamic SQL is when the conditions in the WHERE clause change depending on the execution time. Dynamic SQL is used when the SQL statement cannot be fixed at compile time.
2. When executing DDL statements within a PL/SQL block
DDL statements cannot be executed in anonymous PL/SQL blocks, functions, procedures, or package bodies. However, using dynamic SQL allows execution of DDL statements.
3. When executing ALTER SYSTEM/SESSION commands within a PL/SQL block
Like DDL statements, ALTER SYSTEM/SESSION commands cannot be used directly in PL/SQL, but they can be used with dynamic SQL.
Method
Example
CREATE OR REPLACE FUNCTION FUNC1( W_PRODUCT_ID IN NUMBER ) RETURN VARCHAR2 IS PRODUCT_NAME VARCHAR2(100); BEGIN EXECUTE IMMEDIATE 'SELECT PRODUCT_NAME FROM PRODUCTS WHERE PRODUCT_ID = '|| W_PRODUCT_ID INTO PRODUCT_NAME; RETURN PRODUCT_NAME; END;
Here, the SQL statement is generated as a string and created at runtime.
That is, since the final SQL statement varies depending on the value of W_PRODUCT_ID, this SQL statement is dynamic SQL.
Execution Statement
SELECT FUNC1(1) FROM DUAL
Alternative
The above example has a risk of SQL Injection, so the following dynamic SQL using bind variables is safer.
EXECUTE IMMEDIATE 'SELECT PRODUCT_NAME FROM PRODUCTS WHERE PRODUCT_ID = :id' INTO PRODUCT_NAME USING W_PRODUCT_ID;