Using Execute Immediate to run Dynamic PL/SQL

The purpose of this page is to describe an alternative for Dbms_Sql when you need to execute dynamic PL/SQL within IFS Applications. Execute Immediate is easier to use and sometimes more efficient than using Dbms_Sql. The syntax of Execute Immediate is straight forward, more or less write your piece of PL/SQL and execute it. If possible conditional compilation as described in Dynamic calls to optional components should be used.

Contents

• How Execute Immediate works
• Benefits
• When to use Dbms Sql vs. Execute Immediate

How Execute Immediate works

If the SQL statement is self-contained, requires no bind variables, then we can use Execute Immediate in its simplest form.

EXECUTE IMMEDIATE statement_;

If you have bind variables you need to use the Using clause.

EXECUTE IMMEDIATE statement_ USING IN variable_;

For more details, please study the Oracle documentation about the subject.

Benefits

Below we list the benefits for Execute Immediate over Dbms_Sql

• Easier to understand the code for other developers
  Trying to understand a piece of Pl/SQL code that uses Dbms_Sql can be very hard, because of the complexity of using Dbms_Sql and all of its methods.
• Easier to maintain the code
  If the code is easier to understand it is also easier to maintain, for example to make a bug correction is much easier if you understand what the code tries to do by looking a one small piece of code instead of a whole method.
• Faster execution
  Execute Immediate executes much faster than Dbms_Sql in many scenarios.

Drawbacks

• You can't handle all dynamic PL/SQL with Execute Immediate
   

When to use Dbms_Sql versus Execute Immediate

This section tries to describe when you should use Execute Immediate and when to use Dbms_Sql. Of course you may have other reasons to use the other alternative and vice verse.

You can/should use Execute Immediate when

You must use Dbms_Sql when you don't know