Source: http://www.dbasupport.com/oracle/ora9i/execute_immediate.shtml Reprinted from: LOVEXUEER's BlogEer IMMediate instead of the previous Oracle8i DBMS_SQL Package package. It resolves and immediately performs dynamic SQL statements or non-runtime creation. PL / SQL block. Dynamic creation and execution of SQL statement performance exceeds, Execute Immediate's goal is to reduce corporate costs and get higher performance, which is quite easy to encode, although dbms_sql is still available, but recommended using Execute Immediate, because It has been earned on top of the package. Using Skills 1. Execute Immediate will not submit a DML transaction execution, which should be explicitly submitted if the DML command is processed via Execute Immediate, then you need to explicitly submit or as part of Execute Immediate. If you pass the execute immediate processing DDL command It submits all previously changed data 2. Does not support multiple lines of queries, this interaction will store records in a temporary table (refer to the example below) or use ref.3. When performing a SQL statement, do not use a semicolon When performing a PL / SQL block, a semicolon is used in its tail. 4. In the Oracle manual, these functions are not detailed.
The following example shows all possible aspects of Execute Immediate. I hope to bring you convenience. 5. For Forms developers, when in PL / SQL 8.0.6.3. Version, Forms 6i cannot use this feature. Execute immediate Usage Example 1. Run DDL Statements in PL / SQL Begin Execute Immediate 'Set Role All'; end; 2. Dynamic statement pass (Using clause) Declare l_depnam varcha2 (20): = 'testing'; l_loc varcha2 (10 : = 'Dubai'; Begin Execute Immediate 'Insert Into Dept Values (: 1,: 2,: 3)' Using 50, L_DEPNAM, L_LOC; Commit; End; 3. From Dynamic Specifier Retrieval Value (INTO Sentence) Declare L_cnt varcha2 (20); begin execute immediate 'select count (1) from EMP' INTO L_CNT; DBMS_OUTPUT.PUT_LINE (L_cnt); end; 4. Dynamic call routine. Bind variable parameters used in routines must specify parameters Type. 黓 Type IN type, other types must be explicitly specified Declare L_Routin varcha2 (100): = 'gEN2161.GET_ROWCNT'; L_TBLNAM VARCHAR2 (20): = 'EMP'; L_CNT Number; l_status varcha2 (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; also available% RowType variable Decla re 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 the value. Into clause is used in the USING clause before declare l_dept pls_integer: = 20; l_nam varchar2 (20); l_loc varcha2 (20); begin execute immediate 'Select DName, Loc from Dept Where DEPTNO =: 1 'INTO L_NAM, L_LOC Using L_DEPT; END; 7. Multi-line query options. Use the INSERT statement to populate the temporary table with this option.
