A Quick Guide to Writing Dynamic SQL in PL/SQL

Dynamic SQL refers to SQL statements that are constructed and executed at runtime. Dynamic is the opposite of static. Static SQL (also known as Embedded SQL) refers to SQL statements that are fully specified, or fixed, at the time the code containing that statement is compiled. Dynamic PL/SQL refers to entire PL/SQL blocks of code that are constructed dynamically, then compiled and executed.

There are, broadly, two ways to execute dynamic SQL and PL/SQL:

Native dynamic SQL: use native PL/SQL statements (EXECUTE IMMEDIATE or OPEN FOR) to handle your dynamic SQL needs.

DBMS_SQL: use a built-in package with a big, fat API to parse, execute, etc. your dynamic SQL statements.

Oh, great. Two ways to do it, with the obvious next question: which one should you use?

First, the short answer, then the long answer.

Short Answer

Use native dynamic SQL, which means — almost always — use EXECUTE IMMEDIATE. It is very easy to use and is quite efficient.

There is really just one general use case for DBMS_SQL these days (Oracle Database 11g and higher): method 4 dynamic SQL.

Which brings me to the long answer.

Long Answer

Three are four methods of dynamic SQL:

Method 1: The simplest kind of dynamic statement. Either a DDL statements (e.g., CREATE TABLE) or a non-query DML (update, insert, delete, merge) that has no bind variables.

Examples using EXECUTE IMMEDIATE:

BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE my_table';
END;

(You cannot execute DDL statements natively in PL/SQL, as you could a SELECT. So you must put the statement inside quotes and execute it dynamically.)

PROCEDURE null_out_column (column_in IN VARCHAR2)
IS
BEGIN
   /* Basic protection against SQL injection */
   IF column_in NOT IN (<white list of columns>)  
   THEN
      RAISE_APPLICATION_ERROR (-20000, 'Unable to retrieve data');
   END IF;

   EXECUTE IMMEDIATE
    'UPDATE my_table SET ' || column_in || ' = NULL';
END;

Method 2: Non-query DML statements with one or more bind variables, the number of which is known at compile time.

Example using EXECUTE IMMEDIATE:

PROCEDURE updnumval (
   col_in                     IN       VARCHAR2
 , start_in                   IN       DATE
 , end_in                     IN       DATE
 , val_in                     IN       NUMBER
)
IS
   l_update   varchar2(1000)
      :=    'UPDATE employees SET '
         || col_in
         || ' = :val 
        WHERE hire_date BETWEEN :lodate AND :hidate
          AND :val IS NOT NULL';
BEGIN
   /* Basic protection against SQL injection */
   IF numcol NOT IN ('SALARY', 'COMMISSION_PCT')
   THEN
      RAISE_APPLICATION_ERROR (-20000,
         'Unable to retrieve data');
   END IF;

   EXECUTE IMMEDIATE l_update
               USING val_in, start_in, end_in, val_in;
END;

Notice that I must provide val_in twice. Assignments of variables to placeholders is positional in dynamic SQL.

Because I know the number of placeholders in the string (four), I can use USING to bind the parameters in.

Method 3: a SELECT statement whose select list (number of elements returned by the query) and bind variables are fixed at compile-time.

Example using EXECUTE IMMEDIATE:

FUNCTION best_ever_ranking_in (
   ranking_mview_in IN VARCHAR2, user_id_in IN INTEGER) 
   RETURN INTEGER
IS
   l_ranking     INTEGER;
BEGIN
   EXECUTE IMMEDIATE
        'SELECT MAX (ranking) FROM ' 
      || sys.DBMS_ASSERT.sql_object_name (ranking_mview_in) 
      || ' WHERE user_id = :empid'
      INTO l_ranking
      USING user_id_in;

   RETURN l_ranking;
END;

This is an example from the PL/SQL Challenge. We have a set of materialized views that hold player rankings (refreshed weekly). To get the lifetime best ranking for an individual, I pass in the name of the materialized view and the user ID.

Because I know the number of elements in the SELECT list (1), I can use the INTO clause of EXECUTE IMMEDIATE to get it back.

Because I know the number of placeholders in the string (1), I can use USING to bind the parameter in.

Method 4: "I know what I don't know." At the time I write my code, I don't know how many elements will be in my SELECT list and/or I don't know the number of variables that may need to be bound to placeholders. Wow. I don't know very much, do I?

Method 4 is hard to implement because you have to write very flexible code. And it is extremely difficult (but not impossible) to do this with EXECUTE IMMEDIATE because the INTO and USING clauses are static.

As a result, DBMS_SQL is usually the best implementation mechanism for method 4 dynamic SQL. It provides a very granular API that gives you complete control over every step in the dynamic SQL flow, including: create cursor, parse statement, bind variables, execute statement, get column values, and close cursor.

And that's just great — if you need it. A loooong time ago, before EXECUTE IMMEDIATE was available, DBMS_SQL was mostly just a big pain because of all the code you had to write even for simple scenarios.

Here's an example of using DBMS_SQL to implement a method 3 program that displays the values of any two columns (well, one's of type number, another type string) in employees:

PROCEDURE showemps (
   numcol IN VARCHAR2,
   strcol IN VARCHAR2)
IS
   cur INTEGER := DBMS_SQL.OPEN_CURSOR;
   rec employees%ROWTYPE;
   fdbk INTEGER;
   loc INTEGER;
BEGIN
   /* Basic protection against SQL injection */
   IF numcol NOT IN ('SALARY', 'COMMISSION_PCT')
   THEN
      RAISE_APPLICATION_ERROR (-20000,
         'Unable to retrieve data');
   END IF;

   DBMS_SQL.PARSE
     (cur, 'SELECT ' || numcol || ', ' || strcol || 
            ' FROM employees',
      DBMS_SQL.NATIVE);

   DBMS_SQL.DEFINE_COLUMN (cur, 1, 1);
   DBMS_SQL.DEFINE_COLUMN (cur, 2, 'a', 30);

   fdbk := DBMS_SQL.EXECUTE (cur);

   LOOP
      /* Fetch next row. Exit when done. */
      EXIT WHEN DBMS_SQL.FETCH_ROWS (cur) = 0;
      DBMS_SQL.COLUMN_VALUE (cur, 1, rec.employee_id);
      DBMS_SQL.COLUMN_VALUE (cur, 2, rec.last_name);
      DBMS_OUTPUT.PUT_LINE (
         TO_CHAR (rec.employee_id) || '=' || rec.last_name);
   END LOOP;

   DBMS_SQL.CLOSE_CURSOR (cur);
END;

So pretty clearly you do not want to use DBMS_SQL unless you need to, and method 4 is pretty much exactly when you need to.

Showing you how to use DBMS_SQL for method 4 in a blog post leads to a really, really long blog post. So instead, I suggest you check out the LiveSQL script listed below. It should give you a pretty good feel for at least some of the steps involved. But to summarize:

If you do not know the number of items in the SELECT list, you will need to execute calls to DEFINE_COLUMN within a loop, as in:

   WHILE (l_index IS NOT NULL)
   LOOP
      IF is_string (columns_in, l_index)
      THEN
         DBMS_SQL.define_column (cursor_io,
                                 l_index,
                                 'a',
                                 columns_in (l_index).data_length);
      ELSIF is_number (columns_in, l_index)
      THEN
         DBMS_SQL.define_column (cursor_io, l_index, 1);
      ELSIF is_date (columns_in, l_index)
      THEN
         DBMS_SQL.define_column (cursor_io, l_index, SYSDATE);
      END IF;

      l_index := columns_in.NEXT (l_index);

   END LOOP;

And if you don't know how many bind variables there are, then you will need to execute calls to BIND_VARIABLE in a loop, something like this:

PROCEDURE method4_bind_variables (
   sql_in            IN VARCHAR2
 , placeholders_in   IN DBMS_SQL.varchar2a
 , values_in         IN DBMS_SQL.varchar2a)
IS
   l_cursor     INTEGER := DBMS_SQL.open_cursor;
   l_feedback   PLS_INTEGER;
BEGIN 
   /* WARNING: a program like this should NEVER be available
      directly to an end user - that is, a user should not be
      able to enter a SQL statement and leave it to you to 
      execute it for you! Major SQL injection opening. */

   DBMS_SQL.parse (l_cursor, sql_in, DBMS_SQL.native);

   FOR indx IN 1 .. values_in.COUNT
   LOOP
      DBMS_SQL.bind_variable (l_cursor
                            , placeholders_in (indx)
                            , values_in (indx));
   END LOOP;

   l_feedback := DBMS_SQL.execute (l_cursor);

   DBMS_OUTPUT.put_line (SQL%ROWCOUNT);

   DBMS_SQL.close_cursor (l_cursor);
END;

Mostly, though, you will simply not need to bother with DBMS_SQL, because true method 4 dynamic SQL requirements are rare.

Here are some resources to help you explore dynamic SQL further:

• Practically Perfect PL/SQL Video Playlist on Dynamic SQL

• LiveSQL Script - Method 2

• LiveSQL Script - Method 3

• LiveSQL Script - Method 4

• PL/SQL Documentation

• PL/SQL Challenge Quizzes

• Oracle Magazine Article

• oracle-developer.net article on method 4