Oracle PL/SQL Dynamic SQL Tutorial: Execute Immediate & DBMS_SQL

What is Dynamic SQL?

Dynamic SQL is a programming methodology for generating and running statements at run-time. It is mainly used to write general-purpose and flexible programs where the SQL statements are created and executed at run-time based on the requirement, for example when table names, column lists, or WHERE conditions are not known until the program runs.

Ways to Write Dynamic SQL

PL/SQL provides two ways to write dynamic SQL:

1. NDS – Native Dynamic SQL (the EXECUTE IMMEDIATE and OPEN-FOR statements)
2. DBMS_SQL (a supplied package)

The general rule is simple: if the number and data types of the input and output variables are known at compile time, use Native Dynamic SQL because it is faster and needs less code. When that information is known only at run-time, use the DBMS_SQL package.

NDS (Native Dynamic SQL) – Execute Immediate

Native Dynamic SQL is the easier way to write dynamic SQL. It uses the EXECUTE IMMEDIATE command to create and execute the SQL at run-time. To use this approach, the datatype and number of variables used at run-time must be known beforehand. It also gives better performance and lower complexity when compared to DBMS_SQL.

Syntax

EXECUTE IMMEDIATE dynamic_sql_string
[INTO {variable[, variable]... | record}]
[USING [IN | OUT | IN OUT] bind_argument[, ...]]
[RETURNING INTO bind_argument[, ...]];

• dynamic_sql_string: A string expression (VARCHAR2 or CHAR, not NVARCHAR2/NCHAR) holding a single SQL statement or PL/SQL block.
• INTO clause: Optional. Used only when the dynamic SQL is a single-row SELECT; it captures the returned values into variables or a record. Each selected column needs a type-compatible variable.
• USING clause: Optional. Supplies bind variables. The default mode is IN; OUT and IN OUT are used to receive values back.
• RETURNING INTO clause: Used with DML statements that carry a RETURNING clause, to capture affected-row values into bind arguments.

Example 1: In this example, we fetch the data from the emp table for emp_no ‘1001’ using an NDS statement with a bind variable.

DECLARE
   lv_sql       VARCHAR2(500);
   lv_emp_name  VARCHAR2(50);
   ln_emp_no    NUMBER;
   ln_salary    NUMBER;
   ln_manager   NUMBER;
BEGIN
   lv_sql := 'SELECT emp_name, emp_no, salary, manager FROM emp WHERE emp_no = :empno';
   EXECUTE IMMEDIATE lv_sql
      INTO lv_emp_name, ln_emp_no, ln_salary, ln_manager
      USING 1001;
   DBMS_OUTPUT.PUT_LINE('Employee Name: '   || lv_emp_name);
   DBMS_OUTPUT.PUT_LINE('Employee Number: ' || ln_emp_no);
   DBMS_OUTPUT.PUT_LINE('Salary: '          || ln_salary);
   DBMS_OUTPUT.PUT_LINE('Manager ID: '      || ln_manager);
END;
/

Output

Employee Name  : XXX
Employee Number: 1001
Salary         : 15000
Manager ID     : 1000

Code Explanation:

• Lines 2-6: Declaring the variables.
• Line 8: Framing the SQL at run-time. The SQL contains the bind variable ‘:empno’ in the WHERE condition.
• Lines 9-11: Executing the framed SQL with EXECUTE IMMEDIATE. The INTO clause variables hold the fetched values, and the USING clause supplies the value for the bind variable :empno.
• Lines 12-15: Displaying the fetched values.

Using Dynamic SQL for DDL

Static PL/SQL cannot run DDL such as CREATE, ALTER, or DROP directly. EXECUTE IMMEDIATE solves this by building the statement as a string, which is also handy when an object name is supplied at run-time:

DECLARE
   l_table_name VARCHAR2(30) := 'my_table';
   l_sql_stmt   VARCHAR2(200);
BEGIN
   l_sql_stmt := 'CREATE TABLE ' || l_table_name ||
                 ' (id NUMBER, name VARCHAR2(30))';
   EXECUTE IMMEDIATE l_sql_stmt;
END;
/

Object names (table, column, schema) cannot be passed as bind variables, so they must be concatenated into the string. Always validate such input, for example with DBMS_ASSERT.SIMPLE_SQL_NAME, to avoid SQL injection.

DBMS_SQL for Dynamic SQL

PL/SQL provides the DBMS_SQL package for working with dynamic SQL when the structure of the statement is not known until run-time. The process of creating and executing the dynamic SQL involves the following steps:

• OPEN CURSOR: The dynamic SQL executes like a cursor. To execute the SQL statement, we must first open the cursor.
• PARSE SQL: Parse the dynamic SQL. This checks the syntax and keeps the query ready to execute.
• BIND VARIABLE Values: Assign the values for the bind variables, if any.
• DEFINE COLUMN: Define each column using its relative position in the select statement.
• EXECUTE: Execute the parsed query.
• FETCH VALUES: Fetch the executed values.
• CLOSE CURSOR: Once the results are fetched, close the cursor.

Example 1: In this example, we fetch the data from the emp table for emp_no ‘1001’ using a DBMS_SQL statement. The EXCEPTION block closes the cursor even if an error occurs.

DECLARE
   lv_sql            VARCHAR2(500);
   lv_emp_name       VARCHAR2(50);
   ln_emp_no         NUMBER;
   ln_salary         NUMBER;
   ln_manager        NUMBER;
   ln_cursor_id      NUMBER;
   ln_rows_processed NUMBER;
BEGIN
   lv_sql := 'SELECT emp_name, emp_no, salary, manager FROM emp WHERE emp_no = :empno';
   ln_cursor_id := DBMS_SQL.OPEN_CURSOR;
   DBMS_SQL.PARSE(ln_cursor_id, lv_sql, DBMS_SQL.NATIVE);
   DBMS_SQL.BIND_VARIABLE(ln_cursor_id, ':empno', 1001);
   DBMS_SQL.DEFINE_COLUMN(ln_cursor_id, 1, lv_emp_name, 50);
   DBMS_SQL.DEFINE_COLUMN(ln_cursor_id, 2, ln_emp_no);
   DBMS_SQL.DEFINE_COLUMN(ln_cursor_id, 3, ln_salary);
   DBMS_SQL.DEFINE_COLUMN(ln_cursor_id, 4, ln_manager);
   ln_rows_processed := DBMS_SQL.EXECUTE(ln_cursor_id);
   LOOP
      IF DBMS_SQL.FETCH_ROWS(ln_cursor_id) = 0 THEN
         EXIT;
      ELSE
         DBMS_SQL.COLUMN_VALUE(ln_cursor_id, 1, lv_emp_name);
         DBMS_SQL.COLUMN_VALUE(ln_cursor_id, 2, ln_emp_no);
         DBMS_SQL.COLUMN_VALUE(ln_cursor_id, 3, ln_salary);
         DBMS_SQL.COLUMN_VALUE(ln_cursor_id, 4, ln_manager);
         DBMS_OUTPUT.PUT_LINE('Employee Name: '   || lv_emp_name);
         DBMS_OUTPUT.PUT_LINE('Employee Number: ' || ln_emp_no);
         DBMS_OUTPUT.PUT_LINE('Salary: '          || ln_salary);
         DBMS_OUTPUT.PUT_LINE('Manager ID: '      || ln_manager);
      END IF;
   END LOOP;
   DBMS_SQL.CLOSE_CURSOR(ln_cursor_id);
EXCEPTION
   WHEN OTHERS THEN
      DBMS_SQL.CLOSE_CURSOR(ln_cursor_id);
END;
/

Output

Employee Name  : XXX
Employee Number: 1001
Salary         : 15000
Manager ID     : 1000

Code Explanation:

• Lines 1-8: Variable declaration.
• Line 10: Framing the SQL statement.
• Line 11: Opening the cursor using DBMS_SQL.OPEN_CURSOR, which returns the id of the opened cursor.
• Line 12: After the cursor is opened, the SQL is parsed.
• Line 13: The bind value ‘1001’ is assigned in place of ‘:empno’.
• Lines 14-17: Defining the columns by their relative position: (1) emp_name, (2) emp_no, (3) salary, (4) manager.
• Line 18: Executing the query with DBMS_SQL.EXECUTE, which returns the number of records processed.
• Lines 19-32: Fetching the records in a loop. FETCH_ROWS returns 0 when no rows remain, which exits the loop.
• EXCEPTION block: Ensures the cursor is closed so open cursors do not leak if an error is raised.

NDS vs DBMS_SQL: When to Use Which

Both approaches run SQL at run-time, but they suit different situations:

• Use Native Dynamic SQL (EXECUTE IMMEDIATE / OPEN-FOR) when the number and datatypes of inputs and outputs are known at compile time. It is faster, easier to read, and needs less code.
• Use DBMS_SQL when the structure is unknown until run-time, for instance a query whose number of selected columns or bind variables varies, known as method-4 dynamic SQL, or a statement too large to fit in a single 32K VARCHAR2 variable.

FAQs