by Amar Kumar Padhi
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.
1. EXECUTE IMMEDIATE will not commit a DML transaction
carried out and an explicit commit should be done.
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.
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.
3. Do not use a semi-colon when executing SQL statements, and
use semi-colon at the end when executing a PL/SQL block.
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.
5. For Forms Developers, this feature will not work in Forms
6i front-end as it is on PL/SQL 22.214.171.124.
Example of EXECUTE IMMEDIATE usage
1. To run a DDL statement in PL/SQL.
execute immediate 'set role all';
2. To pass values to a dynamic statement (USING clause).
l_depnam varchar2(20) := 'testing';
l_loc varchar2(10) := 'Dubai';
execute immediate 'insert into dept values (:1, :2, :3)'
using 50, l_depnam, l_loc;
3. To retrieve values from a dynamic statement (INTO clause).
execute immediate 'select count(1) from emp'
4. To call a routine dynamically: The bind variables used for
parameters of the routine have to be specified along with the parameter type.
IN type is the default, others have to be specified explicitly.
l_routin varchar2(100) := 'gen2161.get_rowcnt';
l_tblnam varchar2(20) := 'emp';
execute immediate 'begin ' || l_routin || '(:2, :3, :4); end;'
using in l_tblnam, out l_cnt, in out l_status;
if l_status != 'OK' then
5. To return value into a PL/SQL record type: The same option
can be used for %rowtype variables also.
type empdtlrec is record (empno number(4),
execute immediate 'select empno, ename, deptno ' ||
'from emp where empno = 7934'
6. To pass and retrieve values: The INTO clause should
precede the USING clause.
l_dept pls_integer := 20;
execute immediate 'select dname, loc from dept where deptno = :1'
into l_nam, l_loc
using l_dept ;
7. Multi-row query option. Use the insert statement to
populate a temp table for this option. Use the temporary table to carry out
further processing. Alternatively, you may use REF cursors to by-pass this
l_sal pls_integer := 2000;
execute immediate 'insert into temp(empno, ename) ' ||
' select empno, ename from emp ' ||
' where sal > :1'
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