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;
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;
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;
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;
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;
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).
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).