Feuertip #25: dynamic PL/SQL for the win

You’ve probably all heard of dynamic SQL (and its terrifying cousin, SQL injection). I bet a whole bunch of you have even written a program with dynamic SQL in it. A really good indicator that you are doing so is the appearance of execute immediate in your code (or, heaven forbid, dbms_sql). Here, for example, is the kind of dynamic SQL you probably shouldn’t ever write – a function to calculate the total number of rows in whatever table you specify:

create or replace procedure show_count (p_table in varchar2)
is
begin
   execute immediate 'select count(*) from ' || p_table into l_count;
   dbms_output..put_line ('Total number of rows in ' || p_table || ' = ' || l_count);
end;

Why “shouldn’t”?  Because it saves you a little time typing the code statically (i.e., with the table name hard-coded in it), but it does open up possibilities of SQL injection, and it is simply unnecessarily generic.

There are really just two reasons to use dynamic SQL:

1. You simply don’t have all the information you need at compile-time to construct your SQL statement.
2. A switch to dynamic SQL can save you lots of time, effort, and code (usually by avoiding redundancy).

These same two reasons apply to another way to use execute immediate, and that is to dynamically construct and execute an entire block of PL/SQL code!

In this Feuertip, I offer some tips on writing and running dynamic PL/SQL blocks. Here’s the first tip: the following is not an example of dynamic PL/SQL:

create or replace procedure create_proc (p_table in varchar2)
is
begin
   execute immediate 'create or replace procedure dynproc is begin null; end;';
end;

That is a dynamic DDL statement (data definition language) and is actually dynamic SQL.

Here’s an example of dynamic PL/SQL:

create or replace procedure ru_proc_without_params (p_proc_name in varchar2)
is
begin
   execute immediate 'begin ' || p_proc_name || '; end;';
end;

Ways in which dynamic SQL and dynamic PL/SQL are similar:

• Use execute immediate or dbms_sql, but execute immediate is always preferred.
• The using clause allows you to bind values from your program into placeholders in the string.
• The forall statement will execute dynamic SQL statements and dynamic PL/SQL blocks.
• Overhead of running code dynamically is small and typically of little concern (compared to the elapsed time of the code you are executing)

Ways in which dynamic SQL and dynamic PL/SQL are different:

• Using dynamic PL/SQL in a forall statement does not improve performance.
• Binding placeholders is done by position in dynamic SQL, but by name in dynamic PL/SQL.
• You do not (cannot) use the into clause to get back values from your dynamic block.
• sql%rowcount returns 1 if the block terminates without an exception, 0 otherwise. Doesn’t matter if your procedure executed a SQL statement that modified > 1 row.

When would you find dynamic PL/SQL helpful?

• As with dynamic SQL, you’ll find it helpful first and foremost when you literally don’t have all the information you need when writing and compiling the code. Also…
• The code you need to execute at runtime might not exist at compile-time
• Using dynamic PL/SQL dramatically reduces code volume/repetition

Here’s one demonstration from a (sadly) real-world example, from many years past (I’ll tell the story in our session):

PROCEDURE process_lineitem (line_in IN INTEGER)
-- Literally 1000s of lines of code processing 1000s of ways 
-- this insurance company tried to avoid paying on a claim.
IS
BEGIN
   IF line_in = 1
	THEN
	   process_line1; 

   ELSIF line_in = 2
	THEN
	   process_line2;
   ...
   ELSIF line_in = 2045
   THEN
      process_line2045;
   END IF;
END;

And here’s the replacement for it using dynamic PL/SQL…

PROCEDURE process_lineitem (line_in IN INTEGER)
IS
BEGIN
   EXECUTE IMMEDIATE    
      'BEGIN process_line '|| line_in ||'; END';
END;
/

About the overhead of dynamic PL/SQL…

I mentioned above that dynamic SQL, and dynamic PL/SQL are similar in that the overhead of running code dynamically is usually no big deal.

That’s not to say there’s isn’t a price to be paid. Consider the following silly blocks:

declare
   n number := 1;
begin
   for indx in 1..1000000 loop
      n := n + indx;
   end loop;
   dbms_output.put_line('Static after computing ' || n);
end;

declare
   n number := 1;
begin
   for indx in 1..1000000 loop
      execute immediate 'begin :n := :n + :i; end;'
         using in out n, in indx;
   end loop;
   dbms_output.put_line('Dynamic after computing ' || n);
end;

When I run these blocks on my laptop, the static code takes roughly .05 seconds. The dynamic version took over 5 seconds. TWO ORDERS OF MAGNITUDE SLOWER!

Still, that’s 5 seconds for 1M iterations. So the overhead of an individual dynamic execution is about .000005 seconds. Hopefully, most if not all of your users can tolerate this slowdown. 🙂

When the need arises

Many database developers never even encounter the need for dynamic PL/SQL block execution. I am tempted to say “lucky you!” but actually, I feel more like “how sad.” Because using dynamic PL/SQL to solve a problem means (a) you had an interesting challenge to overcome and (b) you got to step out of the usual “rut” of PL/SQL programming and have some serious fun!

Further study

Oracle documentation on dynamic SQL (including dynamic PL/SQL)

LiveSQL scripts on dynamic PL/SQL

Watch the full Feuertips episode here