Oracle PL SQL Tutorial day 18
Overview
Dynamic SQL is a powerful feature of PL/SQL that allows developers to construct and execute SQL statements dynamically at runtime. This capability is essential when the structure of your SQL queries cannot be determined until runtime—for example, when table names, column names, or WHERE conditions are decided dynamically.
This blog covers:
- - What is Dynamic SQL?
- - Native dynamic SQL vs. DBMS_SQL
- - Use cases and benefits
- - Syntax and real-world examples
- - Tips and best practices
What is Dynamic SQL?
Dynamic SQL refers to SQL statements that are built and executed at runtime, as opposed to static SQL which is fully known at compile time.
Dynamic SQL is necessary when:
- The table or column name is not known until runtime
- You need to build flexible WHERE clauses based on user input
- You want to execute DDL (like CREATE TABLE) in PL/SQL
PL/SQL supports dynamic SQL in two main ways:
1. Native Dynamic SQL — using EXECUTE IMMEDIATE
2. DBMS_SQL package — for more complex scenarios
Native Dynamic SQL with EXECUTE IMMEDIATE
This is the preferred and simplest method for executing dynamic SQL in modern PL/SQL (Oracle 9i and above).
Syntax:
EXECUTE IMMEDIATE <dynamic_sql_string>;
Example 1: Dynamic DDL
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE employees_temp (id NUMBER, name VARCHAR2(50))';
END;
/
Example 2: Dynamic INSERT
DECLARE
v_table_name VARCHAR2(30) := 'employees_temp';
BEGIN
EXECUTE IMMEDIATE 'INSERT INTO ' || v_table_name || ' VALUES (1, ''John'')';
END;
/
Example 3: Dynamic UPDATE with Bind Variables
Below example code show the usage of bund variables. Either we can build whole SQL with values and execute it or we can use bind variables. In case if use while SQL we should be careful about varchar and date values, proper single quotes must be place. To avoid this we can use bind variables it is more convenient and easy to handle
DECLARE
v_sql VARCHAR2(1000);
v_id NUMBER := 1;
v_name VARCHAR2(50) := 'Alice';
BEGIN
v_sql := 'UPDATE employees_temp SET name = :1 WHERE id = :2';
EXECUTE IMMEDIATE v_sql USING v_name, v_id;
END;
/
Example 4: Dynamic SELECT INTO
To move values in PL SQL variables the INTO clause can be use. Define variable first then use into clause. Exception Handling can be added as normal way for NO_DATA_FOUND
DECLARE
v_sql VARCHAR2(1000);
v_name VARCHAR2(50);
v_id NUMBER := 1;
BEGIN
v_sql := 'SELECT name FROM employees_temp WHERE id = :1';
EXECUTE IMMEDIATE v_sql INTO v_name USING v_id;
DBMS_OUTPUT.PUT_LINE('Name: ' || v_name);
END;
/
Using DBMS_SQL for Dynamic SQL
While EXECUTE IMMEDIATE covers 95% of dynamic SQL needs, DBMS_SQL is useful when:
- You don’t know the number of bind variables at compile time
- You need to describe the structure of a result set dynamically
- You want to handle very dynamic SELECT statements
Example: Using DBMS_SQL
DECLARE
v_cursor INTEGER;
v_query VARCHAR2(1000);
v_col_value VARCHAR2(100);
v_status INTEGER;
BEGIN
v_query := 'SELECT name FROM employees_temp WHERE id = :id';
v_cursor := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(v_cursor, v_query, DBMS_SQL.NATIVE);
DBMS_SQL.BIND_VARIABLE(v_cursor, ':id', 1);
DBMS_SQL.DEFINE_COLUMN(v_cursor, 1, v_col_value, 100);
v_status := DBMS_SQL.EXECUTE(v_cursor);
IF DBMS_SQL.FETCH_ROWS(v_cursor) > 0 THEN
DBMS_SQL.COLUMN_VALUE(v_cursor, 1, v_col_value);
DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_col_value);
END IF;
DBMS_SQL.CLOSE_CURSOR(v_cursor);
END;
/
Dynamic Table and Column Names
DECLARE
v_table_name VARCHAR2(30) := 'employees_temp';
v_column_name VARCHAR2(30) := 'name';
v_sql VARCHAR2(1000);
v_result VARCHAR2(50);
BEGIN
v_sql := 'SELECT ' || v_column_name || ' FROM ' || v_table_name || ' WHERE id = :1';
EXECUTE IMMEDIATE v_sql INTO v_result USING 1;
DBMS_OUTPUT.PUT_LINE('Result: ' || v_result);
END;
/
Executing DDL with Dynamic SQL
Data definition language or DDL can be performed using dynamic SQL as well.
Sometimes we need to perform these operation before execution of business logics. Useful during ETL operations, bulk data processing. Below is an example code for DDL operation.
We can perform various DDL like index re build, drop index, drop table etc.
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE employees_temp';
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/
Dynamic SQL with Collections
DECLARE
TYPE emp_tab IS TABLE OF employees_temp%ROWTYPE;
l_emps emp_tab;
BEGIN
EXECUTE IMMEDIATE 'SELECT * FROM employees_temp'
BULK COLLECT INTO l_emps;
FOR i IN 1 .. l_emps.COUNT LOOP
DBMS_OUTPUT.PUT_LINE('Name: ' || l_emps(i).name);
END LOOP;
END;
/
Best Practices for Dynamic SQL
1. Always use bind variables
Prevents SQL injection and improves performance through cursor reuse.
2. Avoid unnecessary dynamic SQL
Use static SQL when possible — it’s faster and safer.
3. Use DBMS_ASSERT for input sanitization
When table names or column names are passed as input.
Example:
v_table := DBMS_ASSERT.SQL_OBJECT_NAME(user_input_table);
4. Handle exceptions properly
Wrap EXECUTE IMMEDIATE in BEGIN...EXCEPTION...END to manage runtime errors.
5. Monitor performance
Dynamic SQL may prevent Oracle from caching execution plans, so monitor with AWR/ASH.
6. Use logging
Log the SQL strings being executed for debugging and traceability.
Common Use Cases
- - Generic ETL scripts that operate on dynamic tables
- - Multi-tenant applications with partitioned schemas
- - Dynamic search filters in reports
- - Schema migrations and DDL generators
- - Auditing systems with dynamic log tables
Summary
Dynamic SQL empowers developers to build flexible and reusable PL/SQL code that adapts to runtime variables such as table names, column names, and WHERE conditions. Whether you're generating DDL, building search filters, or updating tables dynamically, PL/SQL offers robust tools like EXECUTE IMMEDIATE and DBMS_SQL to meet your needs.
For simple use cases, stick with EXECUTE IMMEDIATE. For more complex requirements like dynamic SELECTs with unknown number of columns or bind variable
s, use DBMS_SQL.
Used carefully and responsibly, dynamic SQL turns PL/SQL into a powerful runtime engine that adapts to your business logic needs.
0 Comments