Execute immediate oracle Introduction

Execute immediate replaces DBMS_ SQL package in Oracle8i.
It parses and immediately executes dynamic SQL statements or PL/SQL blocks created when they are not running. The performance of dynamic creation and execution of SQL statements is advanced,

The goal of execute immediate is to reduce enterprise costs and achieve high performance, which is easier to code than before.

DBMS_ SQL is still available, but EXECUTE IMMEDIATE is recommended because it gains more than the package.

-- Tips

1. execute immediate will not commit a DML transaction execution and should be committed explicitly
If you use execute immediate to process DML commands,
Before the execution is complete, you need to submit it explicitly or as part of execute immediate.
If you use execute immediate to process DDL commands, it submits all previously changed data

2. multi-row queries are not supported. This interaction uses a temporary table to store records (see the example below) or REF cursors.

3. Do not use semicolons when executing SQL statements. When executing PL/SQL blocks, use semicolons at the end.

4. These features are not covered in detail in the Oracle manual.
The following example shows all possible ways to use Execute immediate.

5. For Forms developers, this function is not available for Forms 6i in PL/SQL 8.0.6.3.

Execute immediate -- usage example

1. Run DDL statements in PL/SQL

Begin
Execute immediate 'set role all ';
End;

2. Pass values to dynamic statements (USING clause)

Declare
Rochelle depnam varchar2 (20): = 'testing ';
Rochelle loc varchar2 (10): = 'Dubai ';
Begin
Execute immediate 'insert into dept values (: 1,: 2,: 3 )'
Using 50, l_depnam, l_loc;
Commit;
End;

3. Retrieve values from dynamic statements (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. Dynamic call routine. The variable parameters used in the routine must specify the parameter type.
Struct is considered as the IN type. Other types must be explicitly specified.

Declare
L_routin varchar2 (100): = 'gen2161. get_rowcnt ';
L_tblnam varchar2 (20): = 'emp ';
Rochelle 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. Pass the return value to the PL/SQL record type. The % rowtype variable is also available.

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 = 808080'
Into empdtl;
End;

6. Pass and retrieve the value. INTO clause before the USING clause

Declare
Rochelle dept pls_integer: = 20;
Rochelle Nam varchar2 (20 );
Rochelle 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. This option is used to fill the temporary table with the insert statement,
You can also use REF cursors to correct this defect by using temporary tables for further processing.

Declare
MAID: = 2000;
Begin
Execute immediate 'insert into temp (empno, ename) '|
'Select empno, ename from emp' |
'Where sal>: 1'
Using l_sal;
Commit;
End;

For Processing dynamic statements, execute immediate is easier and more efficient than previously used.
When you want to execute dynamic statements, it is more important to properly handle exceptions. You should focus on capturing all possible exceptions.