oracle inline function called multiple times

Question

When calling a function via an inline select statement, when the function is returning a custom type, Oracle seems to execute the function equal to the number of arguments +1. This seems to happen when the select is included as a CTAS or an insert/select.

Has anyone seen this before? Is this an Oracle bug? I would expect the function to be called once per row in the table.

--Inline function gets called for the number of arguments +1

--drop table t
create table t(
  id number,
  l_blob blob
);


insert into t values(1, utl_raw.cast_to_raw('SampleString'));
COMMIT;

create table tmp_ts (c1 timestamp); 

create or replace type test_type as object(
              c1         varchar2(32)
              ,c2  varchar2(32)
        );
/

create or replace FUNCTION test_function (p_blob blob, p_date date)
RETURN test_type
IS
BEGIN

--This could also be a DBMS_OUTPUT.PUT_LINE statement
insert into tmp_ts VALUES (systimestamp);

return test_type(null,null);

END test_function;
/

--0
select count(*) from tmp_ts;

--Call function on 1 row table - function should just insert 1 row into tmp_ts
create table tst_table as
select test_function(l_blob, '25-JAN-09') as c1
from t;

--it actually inserts 3
select count(*) from tmp_ts;

Example where increasing the argument call for the type increases the number of time the function is executed

---

--Same example with more arguements - 6 arguements here

--Inline function gets called for the number of arguments +1

--drop table t
create table t2(
  id number,
  l_blob blob
);


insert into t2 values(1, utl_raw.cast_to_raw('SampleString'));
COMMIT;

create table tmp_ts2 (c1 timestamp); 

create or replace type test_type2 as object(
              c1         varchar2(32)
              ,c2  varchar2(32)
              ,c3  varchar2(32)
              ,c4  varchar2(32)
              ,c5  varchar2(32)
              ,c6  varchar2(32)
        );
/

create or replace FUNCTION test_function2 (p_blob blob, p_date date)
RETURN test_type2
IS
BEGIN

insert into tmp_ts2 VALUES (systimestamp);

return test_type2(null,null,null,null,null,null);

END test_function2;
/

--0
select count(*) from tmp_ts2;

--Call function on 1 row table - function should just insert 1 row into tmp_ts
create table tst_table2 as
select test_function2(l_blob, '25-JAN-09') as c1
from t;

--it actually inserts 7
select count(*) from tmp_ts2;

Any help/feedback is greatly appreciated.

Answer 1

First: It is a bug that you can even perform a DML inside a function which is called in a SELECT Statement. This should raise an exception.

Otherwise Oracle makes absolutely no guarantee how often Functions in a SQL-Select are executed, it could be once per row, ten times per row or just once for the whole query (with caching) - so however often it is called, this conforms to the specifications.

In this special case it will call the function for each attribute of the returning type, since oracle will not insert the object type as one memory-structure, but use the function like a table with multiple columns and read each column individually like this:

INSERT VALUES ( myFunc(x).attribute1, myFunc(x).attribute2 );

The important part: Never make any assumptions about how often a FUNCTION is called when you use it in an SQL Statement!!! At any time the function could be called again by the optimizer, maybe for sampling or caching...

Preferred solution: Pipelined Functions - a pipelined Function can be called like a Table and will only be called once. You can pass in a cursor which the function uses for input processing and do the whole data-transformation and logging and everything in the function.