EXECUTE IMMEDIATE option for Dynamic SQL
and PL/SQL
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.
Usage tips
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 8.0.6.3.
Example of EXECUTE IMMEDIATE usage
1.
To run a DDL statement in PL/SQL.
begin
execute immediate 'set role all';
end;
2.
To pass values to a dynamic statement (USING clause).
declare
l_depnam varchar2(20) := 'testing';
l_loc
varchar2(10) := 'Dubai';
begin
execute immediate 'insert into dept values
(:1, :2, :3)'
using 50, l_depnam, l_loc;
commit;
end;
3.
To retrieve values from a dynamic statement (INTO clause).
declare
l_cnt
varchar2(20);
begin
execute immediate 'select count(1) from emp'
into l_cnt;
dbms_output.put_line(l_cnt);
end;
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.
declare
l_routin varchar2(100) := 'gen2161.get_rowcnt';
l_tblnam
varchar2(20) := 'emp';
l_cnt
number;
l_status
varchar2(200);
begin
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
dbms_output.put_line('error');
end if;
end;
5.
To return value into a PL/SQL record type: The same option can be used for
%rowtype variables also.
declare
type empdtlrec is record (empno number(4),
ename varchar2(20),
deptno number(2));
empdtl empdtlrec;
begin
execute immediate 'select empno, ename, deptno
' ||
'from emp where empno =
7934'
into empdtl;
end;
6.
To pass and retrieve values: The INTO clause should precede the USING clause.
declare
l_dept
pls_integer := 20;
l_nam
varchar2(20);
l_loc
varchar2(20);
begin
execute immediate 'select dname, loc from dept
where deptno = :1'
into l_nam, l_loc
using l_dept ;
end;
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 drawback.
declare
l_sal
pls_integer := 2000;
begin
execute immediate 'insert into temp(empno,
ename) ' ||
' select empno, ename from emp ' ||
' where
sal > :1'
using l_sal;
commit;
end;
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.
No comments:
Post a Comment