Search notes:

Oracle PL/SQL: EXECUTE IMMEDIATE

execute immediate can be used to dynamically execute an SQL statement or PL/SQL block that is stored in a variable or a string.

INTO clause

The into clause selects values into variables:
create table tq84_exec_imm (
  a number,
  b number
);

insert into tq84_exec_imm values (1,2);

declare
  a number;
  b number;
begin

  execute immediate 'select a, b from tq84_exec_imm' into a, b;

  dbms_output.put_line('a: ' || a || ', b: ' || b);

end;
/


drop table tq84_exec_imm purge;
Github repository Oracle-Patterns, path: /PL-SQL/execute_immediate/into.plsql

INTO ROWTYPE

Instead of listing each individual variable separately, the %rowtype idiom can be used to select all values of a row:
create table tq84_exec_imm (
  a number,
  b number
);

insert into tq84_exec_imm values (1,2);

declare
  r tq84_exec_imm%rowtype;
begin

  execute immediate 'select * from tq84_exec_imm' into r;

  dbms_output.put_line('a: ' || r.a || ', b: ' || r.b);

end;
/


drop table tq84_exec_imm purge;

USING OUT clause

using out transfers data that is evaluated in an execute immediate statement out to variables:
Example 1:
declare

  n number;

begin

  execute immediate 'begin :1 := 42; end;' using out n;

  dbms_output.put_line('n: ' || n);

end;
/
Github repository Oracle-Patterns, path: /PL-SQL/execute_immediate/using_out.plsql
Example 2:
declare
  n number;
begin

  execute immediate 'declare l number; begin l := 42; :1 := l; end;' using out n;

  dbms_output.put_line('n: ' || n);

end;
/
Github repository Oracle-Patterns, path: /PL-SQL/execute_immediate/using_out_2.plsql

USING IN OUT clause

With using in out, it is possible to transfer values into and out from an execute immediate statement. Thus, it combines into and using out.
declare

  n number := 21;

begin

  execute immediate 'begin :1 := :1 * 2; end;' using in out n;

  dbms_output.put_line('n: ' || n);

end;
/
Github repository Oracle-Patterns, path: /PL-SQL/execute_immediate/using_in_out.plsql

USING OUT into a nested type

The following example fills and returns a nested table.
create type tq84_number_t as table of number;
/



declare
   number_t tq84_number_t;
begin

   execute immediate 
     'begin :1 := tq84_number_t(42,null,-1.2, 1e7); end;'
   using out number_t;


   for i in 1 .. number_t.count loop
       dbms_output.put_line('i: ' || number_t(i));
   end loop;

end;
/

drop type tq84_number_t;

Filling a nested table (using .extend)

When using collection methods such as .extend, the .extend must be separated by a space from the bind variable:
create type tq84_number_t as table of number;
/


declare
   number_t tq84_number_t;
begin

   execute immediate
   --
   --    Note the space between `:1` and the «extend» keywoard.
   --    Without this space, a
   --      PLS-00110: bind variable '1.EXTEND' not allowed in this context
   --    error would be raised.
   --
     'begin :1 := tq84_number_t(); '      ||
     '  :1 .extend; :1(:1 .count) := 22;' ||
     '  :1 .extend; :1(:1 .count) := 33;' ||
     'end;'
   --
   --    Not the IN OUT.
   --    With an OUT only, an
   --      ORA-06537: OUT bind variable bound to an IN position
   --    will be raised.
   --
   using in out number_t;

   for i in 1 .. number_t.count loop
       dbms_output.put_line('i: ' || number_t(i));
   end loop;

end;
/

drop type tq84_number_t;

Insert statement returning values

The following example creates a table with an identity column and then inserts a record with execute immedate using the out clause to return the generated value of the identity column and the rowid of the inserted record into two variables:
create table tq84_exec_immediate_test (
   id  number generated always as identity,
   num number,
   txt varchar2(20)
);

declare
   inserted_id    number;
   inserted_rowid rowid;
begin
   execute immediate
    q'{insert into tq84_exec_immediate_test
        (num, txt) values
        (:1 , :2 )
       returning
         id, rowid    into :3, :4
      }'
   using
       in  42         ,     'fourty-two',
       out inserted_id, out inserted_rowid;

   dbms_output.put_line('inserted id:    ' || inserted_id);
   dbms_output.put_line('inserted rowid: ' || inserted_rowid);

end;
/

select * from tq84_exec_immediate_test;

drop  table tq84_exec_immediate_test;
rollback;
Github repository Oracle-Patterns, path: /PL-SQL/execute_immediate/select-returning-values.plsql

BULK COLLECT

The following example combines execute immedidate with bulk collect.
First we need an object and table type into which we select («bulk collect») the data:
create or replace type varchar_number as object (
   v varchar2(10),
   i number
)
/

create or replace type t_varchar_number as table of varchar_number
/
We also need some data to select:
create table vn (
   v varchar2(10),
   i number
);

insert into vn values ('uno',1);
insert into vn values ('due',2);
insert into vn values ('tre',3);

commit;
The function that executes an SQL text and bulk collects its result into the table-type:
create or replace function fct_t return t_varchar_number as
   ret t_varchar_number;
begin
   execute immediate
     'select varchar_number(v,i) from vn'
   bulk collect into ret;

   return ret;
end;
/
Finally: use the function:
set serveroutput on size 10000

declare
   x t_varchar_number;
begin
   x := fct_t;

   for r in (select * from table(cast(x as t_varchar_number))) loop
     dbms_output.put_line(r.v || ', ' || r.i);
   end loop;

end;
/

Select statements

In order for a select statement to be executed with execute immediate, at least one of the into clause or bulk collect into clause must be specifed.
Thus, the following statement does not increase the sequence tq84_seq because the select statement is not executed:
begin
   execute immediate 'select tq84_seq.nextval from dual';
end;
/

Strings / apostrophes

If the text to be executed contains apostrophes (the string delimiters), they need to be escaped by doubling them:
execute immedate 'create or replace view tq84_v as
select
   to_char(dt, ''yyyy-mm-dd'') dt
from
   tab';
Of course, this makes it rather tedious to copy paste such statements for debugging purposes. In order to make that easier, q-strings (q'[…]') should be used:
execute immedate q'[
create or replace view tq84_v as
select
   to_char(dt, ''yyyy-mm-dd'') dt
from
   tab'
]';

Iterate over result (21c)

In 21c, it's possible to iterate over the result set produced by execute immediate with a record type and the in values of clause:
set serveroutput on

declare

   stmt varchar2(4000);

   type obj_count is record (
      cnt   number,
      owner varchar2(128)
   );

begin

   stmt := 'select count(*) cnt, owner from dba_objects group by owner';
  
   for rec obj_count IN VALUES OF (
       execute immediate stmt
   ) loop
       dbms_output.put_line(rec.owner || ': ' || rec.cnt);
   end loop;

end;
/
When I mistakenly created an object type instead of using a record type, Oracle threw the error message PLS-00801: internal error [*** ASSERT at file pdw4.c, line 1006; Cannot coerce from type 25 to type 32; Xanon__0x68e2a910__AB[7, 25]]:
create or replace type obj_count as object (
   cnt   number,
   owner varchar2(30)
);
/

declare
   stmt varchar2(4000);
begin
  
   stmt := 'select count(*) cnt, owner from dba_objects group by owner';

   for rec obj_count in values of (
       execute immediate stmt
   ) loop
       dbms_output.put_line(rec.owner || ': ' || rec.cnt);
   end loop;

end;
/

See also

In a stored procedure that is defined with authid definer, executing an alter session set current_schema = … with execute immediate does not affect the scope in which execute immediate is executed, but it does if the procedure is defined with authid current_schema, see this example and this research).
The DBMS_SQL package.
PL/SQL
The SQL Server equivalent of execute immediate is sp_executesql.
dbms_utility.exec_ddl_statement
Using execute immediate might result in a ORA-24344: success with compilation errors which indicates that the statement executed by execute immediate was syntactically correct, but logically invalid (for example because a table is missing).
If a statement being executed with execute immediate throws such ORA-24344: success with compilation errors error message in Oracle 19 (tested on Version 19.21), the PL/SQL block displays an interesting behavior.
Trying to use execute immediate with an SQL statement that has a trailing semicolon might end up throwing an ORA-00922: missing or invalid option error.

Index