Dynamic SQL FAQs

1. What is Dynamic SQL in Oracle?

Dynamic SQL is SQL code constructed and executed at runtime, rather than fixed at compile time.

• Useful when table names, column names, or conditions are not known until runtime.
• Allows flexible and reusable queries.

2. When should you use Dynamic SQL?

• Table or column names are dynamic
• Conditions vary based on user input
• Executing DDL statements (CREATE, ALTER, DROP) from PL/SQL
• Building generic procedures/functions for multiple tables

3. What are the types of Dynamic SQL?

1.    Native Dynamic SQL (NDS) – uses EXECUTE IMMEDIATE or OPEN FOR; preferred for single statements; good for DML, DDL, single/multi-row queries.

2.    DBMS_SQL Package – older, supports complex statements, binds, multi-row processing; more verbose.

4. How to execute a simple DML using EXECUTE IMMEDIATE?

DECLARE

   v_sql VARCHAR2(200);

BEGIN

   v_sql := 'UPDATE employees SET salary = salary * 1.1 WHERE department_id = 10';

   EXECUTE IMMEDIATE v_sql;

END;

5. How to use Dynamic SQL with bind variables?

DECLARE

   v_sql VARCHAR2(200);

   v_dept NUMBER := 10;

BEGIN

   v_sql := 'UPDATE employees SET salary = salary * 1.1 WHERE department_id = :dept_id';

   EXECUTE IMMEDIATE v_sql USING v_dept;

END;

• Improves performance and prevents SQL injection.

6. How to fetch results dynamically?

Single row:

DECLARE

   v_sql VARCHAR2(200);

   v_name employees.first_name%TYPE;

BEGIN

   v_sql := 'SELECT first_name FROM employees WHERE employee_id = :id';

   EXECUTE IMMEDIATE v_sql INTO v_name USING 101;

   DBMS_OUTPUT.PUT_LINE(v_name);

END;

Multiple rows:

DECLARE

   TYPE emp_tab IS TABLE OF employees.employee_id%TYPE;

   v_emp_ids emp_tab;

BEGIN

   EXECUTE IMMEDIATE 'SELECT employee_id FROM employees WHERE department_id = 10'

   BULK COLLECT INTO v_emp_ids;

END;

7. How to execute DDL statements dynamically?

BEGIN

   EXECUTE IMMEDIATE 'CREATE TABLE temp_table (id NUMBER, name VARCHAR2(50))';

END;

DDL must be executed dynamically; it cannot run directly in PL/SQL blocks.

8. Difference between EXECUTE IMMEDIATE and DBMS_SQL

Feature	EXECUTE IMMEDIATE	DBMS_SQL
Ease of use	Simple	Verbose
Single-row fetch	Supported	Supported
Multi-row fetch	Supported with BULK COLLECT	Supported
DDL execution	Supported	Supported
Dynamic table/column	Limited	Flexible
Preferred in 12c+	Yes	Legacy

9. Performing DML on dynamic tables/columns

DECLARE

   v_table VARCHAR2(30) := 'employees';

   v_sql VARCHAR2(200);

BEGIN

   v_sql := 'UPDATE ' || v_table || ' SET salary = salary * 1.05';

   EXECUTE IMMEDIATE v_sql;

END;

Validate table/column names to avoid SQL injection.

10. Common mistakes

• Concatenating user input directly → SQL injection
• Not using bind variables → performance hit
• Forgetting to handle exceptions
• Misusing DBMS_SQL when EXECUTE IMMEDIATE is sufficient

11. Improving Dynamic SQL performance

• Use bind variables
• Minimize context switches (avoid loops)
• Prefer EXECUTE IMMEDIATE over DBMS_SQL
• Cache repeated SQL strings

12. Returning REF CURSOR dynamically

DECLARE

   rc SYS_REFCURSOR;

BEGIN

   OPEN rc FOR 'SELECT employee_id, first_name FROM employees WHERE department_id = 10';

END;

Useful for generic query procedures.

13. Best practices

• Always use bind variables
• Validate dynamic table/column names
• Prefer EXECUTE IMMEDIATE unless complex parsing is needed
• Use BULK COLLECT for multiple rows
• Handle exceptions (NO_DATA_FOUND, TOO_MANY_ROWS, missing table)