Execute immediate options for dynamic SQL and PL/SQL

Source: http://www.dbasupport.com/oracle/ora9i/execute_immediate.shtml
Reposted from: lovexueer's blog

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 the enterprise cost and obtain high performance, which is easier to code than before. although dbms_ SQL is still available, 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, You need to submit the command explicitly or as part of execute immediate before it is completed. 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 Bind Variable Parameter used in the routine must specify the parameter type. The struct parameter is considered as the in type, and 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, use the temporary table for further processing, or use ref cursors to correct this defect.
 
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. we should focus on capturing all possible exceptions.