 |
Web Hosting by Netfirms | Free Domain Names by Netfirms |
|
|
|
A m Z o n e
EXECUTE IMMEDIATE
Execute Immediate is the replacement for DBMS_SQL package in Oracle 8i (8.1.5). 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 because of its benefits over the package.
Execute immediate will not commit the transaction carried out and an explicit commit should be done. Multi-row queries are not supported for returning values, alternative is to use a temp table to store the records (see example below). It will not work in Forms 6i front-end as it is on PL/SQL 8.0.6.3. Do not use semi-colon when executing SQL statements, and use semi-colon when executing a PL/SQL block.
This feature is not covered at large in the Oracle Manuals. I have mentioned below examples of all possible ways of using Execute immediate. Hope it is handy.
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. Into clause should precede 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 insert statement to populate a temp table for this option. Use the temp table to carry out further processing.
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;
Courtesy
Oracle Manuals
Freelance articles on the net
This article was hosted on 21 sep 02.
Press the Back button of you Browser to go to previous page.
Home