Execute immediate options for dynamic SQL and Pl/sql

Dynamic execute immediate replaces the previous Oracle8i Dbms_sql package package. It parses and immediately executes a dynamic SQL statement or a pl/sql block that is not created at run time. Dynamic creation and execution of SQL statements advanced performance, execute IMMEDIATE's goal is to reduce enterprise costs and achieve higher performance, which is fairly easy to code before. Although Dbms_sql is still available, it is recommended that execute IMMEDIATE be used because the proceeds are on top of the package.


Use Tips



1. Execute immediate will not submit a DML transaction execution and should explicitly commit
If you are handling DML commands through execute immediate, you need to explicitly commit or as part of execute immediate yourself before you finish. If the DDL command is processed through execute immediate, it submits all previously changed data



2. Queries that return multiple rows are not supported, and this interaction uses a temporary table to store the records (refer to the example below) or use ref cursors.



3. When executing SQL statements, do not use semicolons, when the Pl/sql block is executed, with a semicolon at its tail.



4. In the Oracle manual, these features are not covered in detail. The following example shows all the possible aspects of using execute immediate. Hope to bring you convenience.



5. For forms developers, the forms 6i cannot use this feature when in Pl/sql 8.0.6.3. Version.



EXECUTE Immediate Usage Example



1. Run DDL statements in Pl/sql


Begin
Execute immediate ' set role all ';
End



2. Assign values to dynamic statements (USING clause)


Declare
L_depnam varchar2: = ' testing ';
L_loc Varchar2 (a): = ' Dubai ';
Begin
Execute immediate ' INSERT INTO dept values (: 1,: 2,: 3) '
Using L_depnam, L_loc;
Commit
End



3. Retrieving values from dynamic statements (into clauses)


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 invocation routines. The parameter type must be specified for the binding variable argument used in the routine. 黓 thinks in type, other types must explicitly specify



Declare
L_routin varchar2 (MB): = ' gen2161.get_rowcnt ';
L_tblnam varchar2: = ' 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. Pass the return value to the Pl/sql record type; You can also use the%rowtype variable


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. Pass and retrieve values. The INTO clause is used before 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. Multiline query options. Use the INSERT statement to populate the temporary table with the temporary table for further processing, or you can use ref cursors to correct this shortcoming.

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



For processing dynamic statements, EXECUTE immediate is easier and more efficient than it might have been before. When intent to execute dynamic statements, it is more important to handle the exception appropriately. Attention should be paid to capturing all possible exceptions.