Execute immediate in Oracle

Execute immediate replaces the DBMS_ SQL package in Oracle. The following describes how to use EXECUTE IMMEDIATE in Oracle for your reference.

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,
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   
  
  
   l_depnam varchar2(20) := 'testing';   
  
  
   l_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';   
  
  
   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. 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 = 7934'   
  
  
into empdtl;   
  
  
end;  
 
 

6. Pass and retrieve the value. INTO clause 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. 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   
  
  
   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 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.

Oracle authorization statement

Oracle user syntax Modification

Three methods for Oracle Authentication

Oracle Default User Password Problems

Common Oracle cursor attributes