Skip to content

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.

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

  • The dynamic SQL statement retrieves rows into records.
  • You want to use the SQL cursor attribute %FOUND, %ISOPEN, %NOTFOUND, or %ROWCOUNT after issuing a dynamic SQL statement that is an INSERT, UPDATE, DELETE, or single-row SELECT statement.

You must use Dbms_Sql when you don't know

  • SELECT list
  • What placeholders in a SELECT or DML statement must be bound