using execute immediate in pl/sql – the new oracle dba reference

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