DDL StatementsDynamic SQL from PL Sql (Execute Immediate)

{\rtf1\ansi\ansicpg1252\deff0\deflang16393{\fonttbl{\f0\froman\fprq2\fcharset0 Times New Roman;}{\f1\fnil\fcharset0 Calibri;}}
{\stylesheet{ Normal;}{\s1 heading 1;}}
{\*\generator Msftedit 5.41.21.2509;}\viewkind4\uc1\pard\li720\lang1033\f0\fs22 EXECUTE IMMEDIATE is the replacement for DBMS_SQL package from Oracle 8i onwards. It parses and immediately executes a dynamic SQL statement or a PL/SQL block created on the fly. Dynamically created and executed SQL statements are performance overhead; EXECUTE IMMEDIATE aims at reducing the overhead and give better performance. It is also easier to code as compared to earlier means. The error messages generated when using this feature are more user friendly. Though DBMS_SQL is still available, it is advisable to use EXECUTE IMMEDIATE calls because of its benefits over the package.\par
\pard\keepn\s1\fi720\b Usage tips\par
\pard\li720\b0 1. EXECUTE IMMEDIATE will not commit a DML transaction carried out and an explicit commit should be done. \par
If the DML command is processed via EXECUTE IMMEDIATE, one needs to explicitly commit any changes that may have been done before or as part of the EXECUTE IMMEDIATE itself. If the DDL command is processed via EXECUTE IMMEDIATE, it will commit all previously changed data.\par
2. Multi-row queries are not supported for returning values, the alternative is to use a temporary table to store the records (see example below) or make use of REF cursors.\par
3. Do not use a semi-colon when executing SQL statements, and use semi-colon at the end when executing a PL/SQL block.\par
4. This feature is not covered at large in the Oracle Manuals. Below are examples of all possible ways of using Execute immediate. Hope it is handy.\par
For Forms Developers, this feature will not work in Forms 6i front-end as it is on PL/SQL 8.0.6.3.\par
\pard\fi720 Example\par
\pard\li1440 declare\par
l_depnam varchar2(20) := 'testing';\par
l_loc varchar2(10) := 'Dubai';\par
begin\par
execute immediate 'insert into dept values (:1, :2, :3)'\par
using 50, l_depnam, l_loc;\par
commit;\par
end;\par
\pard\par
\pard\li720 EXECUTE IMMEDIATE is a much easier and more efficient method of processing dynamic statements than could have been possible before. As the intention is to execute dynamic statements, proper handling of exceptions becomes all the more important. Care should be taken to trap all possible exceptions.\par
\pard\par
\pard\sa200\sl276\slmult1\lang9\f1\par
}