Wednesday, July 27, 2016

A quick guide to writing dynamic SQL in PL/SQL


[I responded to a tweet today about why you would ever use DBMS_SQL. The answer is: for method 4 dynamic SQL implementations - and that prompted me to write a post on the topic.]

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.

Since I know the number of placeholders in the string (4), 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.

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

Since 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, 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
PL/SQL Documentation
PL/SQL Challenge Quizzes
Oracle Magazine Article
oracle-developer.net article on method 4

13 comments:

  1. Well, as usual - my extra 2 cents (sorry, cannot skip my favorite topic :-) ).

    DBMS_SQL also can help if in your environment there is a lot of CURSOR variables floating around. By using DBMS_SQL.TO_CURSOR_NUMBER and DBMS_SQL.TO_REFCURSOR you can, for example, look inside of any opened cursor and describe its columns without disturbing anything.

    ReplyDelete
    Replies
    1. Good point, Misha. Those additions to DBMS_SQL in 11.1 can be very handy - though it seems like they still mostly come back to helping with method 4 scenarios, and simply reducing how many of the DBMS_SQL built-ins you need to get the job done. Here are two links to LiveSQL scripts that demonstrate these subprograms: https://livesql.oracle.com/apex/livesql/file/content_C1W23DSC7WZM6WY23PBAJZI2W.html and https://livesql.oracle.com/apex/livesql/file/content_C1W23DSC9K441CR5RULGGBVW4.html

      Delete
  2. This comment has been removed by the author.

    ReplyDelete
  3. Hello Steven,

    A small technical issue:

    In the list of resources at the end of the post,
    the URL-s for the LiveSQL Method 2 and Method 3 are the same.

    Cheers & Best Regards,
    Iudith

    ReplyDelete
    Replies
    1. Thanks, Iudith. I have removed the Method 3 link right now. Actually, there is no such script there....yet. I will have it there tomorrow, and then will update this post.

      Delete
  4. The examples for methods 2, 3 and 4 are all open to SQL injection. Some DBMS_ASSERTs wouldn't go amiss.

    ReplyDelete
    Replies
    1. Great point, Kevan. I will take a look at adding that. Or I have a waaaay better idea: puleaze post replacements for the above examples that are fully fortified!

      Delete
    2. What do you think, Kevan? Do you have other suggestions for improving the post in this regard or others?

      Delete
  5. Hello, Steven. For some reasons missed this post in my feed. It seems, you haven't listed one of the most usefull feautures of DBMS_SQL: DBMS_SQL.return_result.

    ReplyDelete
  6. Great point, Arelyo. Please share with us what you like about return_result and how you are using it!

    ReplyDelete
    Replies
    1. Hello, Seven. Maybe, the best example will be from Oracle docs: https://docs.oracle.com/database/121/JJDBC/getsta.htm#JJDBC29004
      I know that nowadays most solutions use some kind of ORM tools, but for pity when you work with enterprise applications you face outdated design decisions. One of my previous projects was the kind of those, so implicit results feature simplify our code a lot.

      Delete