TechRepublic’s Oracle newsletter covers automating Oracle utilities, generating database alerts, solving directed graph problems, and more. Automatically subscribe today!

Developers often dismiss dynamic SQL used in PL/SQL programs
for two reasons. One reason is that it’s riskier because the SQL statements
aren’t parsed until runtime, so it’s more difficult to catch simple syntax
errors. Also, many attempts at dynamic SQL run into performance problems, and
the complexity of simply writing dynamic SQL give a negative impression on
dynamic SQL.

Listing A compares two alternatives to insert a range of numbers into a table. In my tests, the second PL/SQL block takes about six times
as long as the first block. However, this isn’t really a fair comparison
because it violates one of the golden rules of dynamic SQL: Never concatenate
when you can bind. The constant concatenation means that the 10,000 independent
SQL statements need to be parsed, resolved, and executed. The native PL/SQL
insert statement in the first example actually generates a recursive SQL
statement—’insert into mytestvalues(:1)’—and binds
the values on each iteration.

Listing B offers
a better comparison with dynamic SQL. This code runs about twice as slow as the first block. That’s
three times as fast as the second example. However, the dynamic SQL is still at
a disadvantage. The execute immediate still does its parse, but it
uses SQL sharing to resolve the statement.

It would be ideal if PL/SQL would allow us to create the
cursor during OPEN, and then have some new command that allowed us to EXECUTE <cursor> USING …. Even in
Oracle 10g, there is no such command. I’ve been watching native dynamic SQL for
a long time, and I still find that it’s not as powerful as DBMS_SQL in these cases.

The PL/SQL user’s guide warns that the performance of
DBMS_SQL isn’t as good as EXECUTE IMMEDIATE, but let’s try it anyway. Listing C suffers
the same problem as the second example—it uses concatenation. It also turns out
that the PL/SQL manual is correct in this case. Example 4 in Listing C took seven times as
long as the first example. It’s true that DBMS_SQL is slower than EXECUTE
IMMEDIATE, but EXECUTE IMMEDIATE still isn’t as flexible as DBMS_SQL. Example 5 in Listing C is a pleasant surprise—it finished in about the
same time as the first example. It’s going through nearly the same process as
the first example; it’s obviously more difficult to code and more error prone
but, for many tasks, dynamic SQL may be necessary.

As a further comparison, the equivalent Java stored
procedure, (which is always dynamic) takes about the same time as the original
PL/SQL and the DBMS_SQL with binding. View
Listing D.