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 anINSERT
,UPDATE
,DELETE
, or single-rowSELECT
statement.
You must use Dbms_Sql when you don't know
SELECT
list- What placeholders in a
SELECT
or DML statement must be bound