Execute option analysis for dynamic and PL/SQL

The execute immediate replaces the previous Oracle8i Dbms_sql package. It parses and executes a dynamic SQL statement or a PL/SQL block that is not created at runtime. Dynamically create and execute SQL statements performance ahead, execute The goal of IMMEDIATE is to reduce enterprise costs and get higher performance, which is quite easy to code than it used to be. Although Dbms_sql is still available, execute IMMEDIATE is recommended because it gains revenue on the package.

Tips for using
1. Execute immediate will not commit a DML transaction execution and should be explicitly committed. If you are processing DML commands through execute immediate, you need to either explicitly commit before completing or as part of the execute immediate yourself. 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 the interaction uses temporary tables to store records (see examples below) or ref cursors.

3. When executing SQL statements, do not use semicolons when you execute a PL-SQL block with a semicolon at its tail.

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

5. For the forms developer, the forms 6i cannot use this feature in the PL/SQL 8.0.6.3. Version. EXECUTE Immediate Usage Example 1. Running DDL statements in PL/SQL

BEGIN
EXECUTE IMMEDIATE ' Set role all ';
END;

2. Passing a value to a dynamic statement (USING clause)

DECLARE
L_depnam VARCHAR2 (): = ' testing ';
L_loc VARCHAR2 (Ten): = ' Dubai ';
BEGIN
EXECUTE IMMEDIATE ' INSERT INTO dept values (: 1,: 2,: 3) ' using 50,
L_depnam, L_loc;
COMMIT;
END;

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

DECLARE
L_routin VARCHAR2 (+): = ' 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;%rowtype variables are 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 = 7934 '
into EMPDTL;
END;

6. Pass and retrieve the value. 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 for this option to populate the temporary table, use the temporary table for further processing, or use REF cursors to correct the flaw.

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 handling dynamic statements, the EXECUTE immediate is much easier and more efficient than it might have been in the past. It is more important to handle exceptions appropriately when attempting to execute dynamic statements. You should focus on capturing all possible exceptions.

Execute option analysis for dynamic and PL/SQL