Execute Immediate

execute immediate 'sql-statement';
execute immediate 'select-statement' into returned_1, returned_2..., returned_n;

execute immediate 'sql-statement' using [in|out|in out] bind_var_1, [in|out|in out] bind_var_2 ... [in|out|in out] bind_var_n;
execute immediate 'select-statement' into returned_1, returned_2..., returned_n  using [in|out|in out] bind_var_1, [in|out|in out] bind_var_2 ... [in|out|in out] bind_var_n;

execute immediate 'sql-statement' returning into var_1;

execute immediate 'sql-statement' bulk collect into index-by-var;

execute immediate allows to execute a dynamic SQL statement. This statement is a string.

bind-var-n cannot be the literal null. Use a dummy variable instead whose value is null.

Be sure to also read Why is dynamic SQL bad when you read this article.

Also, execute immediate is only available since 8.1, so, on an older version, use dbms_sql instead.

The returning clause can only be used with insert, update and delete statements. If used for other statements, a ORA-06547 is thrown.

In the following example, we create a function (count_in_table) which can be used to count records that satisfy a certain condition in a table whose name is unknown at the time of the creation of the function.

attr:
The name of the column whose value must match attrval in order for the record to be counted
attrval:
The value that attr is compared against
tbl:
The name of the table.

set feedback off
set linesize 120
set pagesize 0

create or replace function count_in_table
  (attr in varchar2, attrval in varchar2, tbl in varchar2)
  return number
is
  cnt number;
begin
  execute immediate 'select count(1) from ' || tbl || ' where ' || attr || ' = :a' into cnt using attrval;
  return cnt;
end;
/

Here's a table (foo) that will later be used in count_in_table.

create table foo (
  bar varchar2(10),
  baz varchar2(10)
);

insert into foo values ('orange'    , 'banana'    );
insert into foo values ('kiwi'      , 'apple'     );
insert into foo values ('pear'      , 'strawberry');
insert into foo values ('pear'      , 'pear'      );
insert into foo values ('orange'    , 'apple'     );
insert into foo values ('pear'      , 'banana'    );
insert into foo values ('apple'     , 'strawberry');
insert into foo values ('strawberry', 'kiwi'      );

Now, we call count_in_table for each record in the table foo to find out, how often the values in foo's column appear in the table:

column countbar format 99
column bar      format a10
column countbaz format 99
column baz      format a10
select count_in_table('bar',bar,'foo') countbar ,bar,', ',count_in_table('baz',baz,'foo') countbaz, baz from foo;

This select statement returns:

       2 orange     ,         2 banana
       1 kiwi       ,         2 apple
       3 pear       ,         2 strawberry
       3 pear       ,         1 pear
       2 orange     ,         2 apple
       3 pear       ,         2 banana
       1 apple      ,         2 strawberry
       1 strawberry ,         1 kiwi

This is to be interpretated as: orange appers twice in bar, banana appears twice in baz, kiwi appears once in bar and so on.

Cleaning up:

drop table foo;
drop function count_in_table;

Note the using attrval notation above. This construct allows to set the value for a bind variable (:a)

Links

Bulk collect together with execute immediate.


	René Nyffenegger's collection of things on the web
	René Nyffenegger on Oracle - Most wanted - Feedback - Follow @renenyffenegger