Do you REALLY need that SQL to be dynamic?

Dynamic SQL means a SQL statement that is constructed, parsed and executed "dynamically" at run time (vs. "statically" at compile time).

It's very easy to write static SQL in PL/SQL program units (one of the great joys of working with this database programming language). It's also quite easy to implement dynamic SQL requirements in PL/SQL.

But that doesn't mean you should. The bottom line regarding dynamic SQL is:

Construct and execute SQL at runtime only when you have to.

There are several good reasons to avoid unnecessary dynamic SQL:

1. Security: dynamic SQL opens up the door to SQL injection, which can lead to data corruption and the leaking of sensitive data.
2. Performance: while the overhead of executing dynamic SQL has gone way down over the years, it is certainly still faster to use static SQL.
3. Maintainability: the code you write to support dynamic SQL is more - literally more code - and harder to understand and maintain.

Sometimes the misuse of dynamic SQL is obvious. Consider the following:

CREATE OR REPLACE FUNCTION name_from_id (id_in IN INTEGER)
   RETURN VARCHAR2
   AUTHID DEFINER
IS
   l_the_name   the_table.the_name%TYPE;
BEGIN
   EXECUTE IMMEDIATE 'select the_name from the_table where id = ' || id_in
      INTO l_the_name;

   RETURN l_the_name;
END;
/

The developer apparently believed, however, that since the value of the ID can change, it needs to be concatenated to the SQL statement, so it's gotta be done dynamically. Ha! There's nothing dynamic about this query. It should be rewritten to:

CREATE OR REPLACE FUNCTION name_from_id (id_in IN INTEGER)
   RETURN VARCHAR2
   AUTHID DEFINER
IS
   l_the_name   the_table.the_name%TYPE;
BEGIN
   SELECT the_name
     INTO l_the_name
     FROM the_table
    WHERE id = id_in;

   RETURN l_the_name;
END;
/

Often, the need for dynamic SQL is compelling at first, but then disappears with a little bit of analysis. Consider this function:

CREATE OR REPLACE FUNCTION name_from_id (table_in   IN VARCHAR2,
                                         id_in      IN INTEGER)
   RETURN VARCHAR2
   AUTHID DEFINER
IS
   l_the_name   VARCHAR2 (32767);
BEGIN
   EXECUTE IMMEDIATE
      'select the_name from ' || table_in || ' where id = ' || id_in
      INTO l_the_name;

   RETURN l_the_name;
END;
/

Well, heck, if I don't know the table name till run time, I sure can't use a static SELECT statement, right? Of course, right!

So, first of all, if this code is really and truly necessary, you need to think about SQL injection.

1. Is the table name provided directly by the user? You should never allow user input to be stuffed directly into your dynamic statement. They could enter all sorts of nasty stuff.

2. Even if not passed directly from user fingertips to EXECUTE IMMEDIATE, you should use DBMS_ASSERT to make sure that the table name passed in is a valid DB object, as in:

CREATE OR REPLACE FUNCTION name_from_id (table_in   IN VARCHAR2,
                                         id_in      IN INTEGER)
   RETURN VARCHAR2
   AUTHID DEFINER
IS
   l_the_name   VARCHAR2 (32767);
BEGIN
   EXECUTE IMMEDIATE
      'select the_name from ' || 
         SYS.DBMS_ASSERT.sql_object_name (table_in) || 
      ' where id = :id'
      INTO l_the_name
      USING id_in;

   RETURN l_the_name;
END;
/

Great, so the function is less vulnerable than before to injection. But does it really need to be dynamic?

The only way to answer that question is to check and see where and how the function is used. I could do a text search through my code (via an editor like Sublime) or an object search (in SQL Developer). I could also use PL/Scope if I'd gathered identifier information across my code base.

Suppose after doing my analysis, I find that the function is called twice as follows:

l_name := name_from_id (table_in => 'TABLE1', id_in => l_id);

l_recent_name := name_from_id (table_in => 'TABLE2', id_in => l_most_recent_id);

OK, I could shrug and say "Yep, two different table names. I need to use dynamic SQL."

But that would be a mistake. What I should think and say is:

"What? Just two different tables? I don't need dynamic SQL for that. That's just laziness."

Instead I could do either of the following:

1. Create two different functions.
2. Change to static SQL inside the function.

Two Different Functions

Really, why not? It's so easy and fast to write PL/SQL functions that call SQL. Just change to:

CREATE OR REPLACE FUNCTION name_from_table1_id (id_in IN INTEGER)
   RETURN VARCHAR2
   AUTHID DEFINER
IS
   l_the_name table1.the_name%TYPE;
BEGIN
   SELECT the_name
     INTO l_the_name
     FROM table1
    WHERE id = id_in;

   RETURN l_the_name;
END;
/

CREATE OR REPLACE FUNCTION name_from_table2_id (id_in IN INTEGER)
   RETURN VARCHAR2
   AUTHID DEFINER
IS
   l_the_name table2.the_name%TYPE;
BEGIN
   SELECT the_name
     INTO l_the_name
     FROM table2
    WHERE id = id_in;

   RETURN l_the_name;
END;
/

One Function, No Dynamic SQL

So you don't want two, three many functions for the "same" functionality (get name for ID). Fine, keep them all in one subprogram, but move the conditional logic inside.

CREATE OR REPLACE FUNCTION name_from_id (table_in   IN VARCHAR2,
                                         id_in      IN INTEGER)
   RETURN VARCHAR2
   AUTHID DEFINER
IS
   l_the_name   VARCHAR2 (32767);
BEGIN
   CASE table_in
      WHEN 'TABLE1'
      THEN
         SELECT the_name
           INTO l_the_name
           FROM table1
          WHERE id = id_in;
      WHEN 'TABLE2'
      THEN
         SELECT the_name
           INTO l_the_name
           FROM table2
          WHERE id = id_in;
      ELSE
         raise_application_error (
            -20000,
            'name_from_id does not support fetching from ' || table_in);
   END CASE;

   RETURN l_the_name;
END;
/

Sure, sometimes you really do need to use EXECUTE IMMEDIATE and dynamic SQL. In those situations, make sure you minimize the attack surfaces for SQL injection. Make sure you've got strong exception handling and logging.

But do make certain that this extra "technical debt" is necessary. There's a good chance you can get by without dynamic SQL. If so, your users, your fellow developers and your manager will all thank you!