Thanks for the question, Jos.
Asked: March 25, 2022 - 9:52 am UTC
Last updated: March 25, 2022 - 4:32 pm UTC
Version: 19c, both Standard and Enterprise Edition
Viewed 10K+ times! This question is 
You Asked
Hi TOM,
We bumped into an issue, which we boiled down to a function being called multiple times, when we expected it to be called only once.
The demo script shows that the function is called once when invoking the function in a `select from dual` query, but twice if that query is wrapped in a query that extracts the individual properties from the result object. If more properties are added and extracted, the function is called more times as well.
We expected the function to be called once in the inner query, after which the outer query would just read properties from the single returned object instance.
We noticed this on 19c Enterprise, but experienced the same in 19c Standard. The LiveSQL (running on 19c as well) also shows the same effect.
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.13.0.0.0
Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production
Version 19.13.0.0.0
The LiveSQL running on 19c Enterprise Edition - 19.8.0.0.0 shows the same results
We did find a workaround, by making the function return a table type. It's good enough to continue with, but it does semantically hide the fact that the function will return exactly one object, so it's not ideal.
We're curious why this happens, and of course also like to know the 'proper' way to do this without using this table type workaround.
Regards,
Jos
with LiveSQL Test Case:
and Chris said...
Each expression in the select clause is a new call to the function. You can see this with a regular function call:
create or replace function f
return int as
begin
dbms_output.put_line('Executing function');
return 1;
end f;
/
select n, n, n
from (
select f n from dual
);
N N N
---------- ---------- ----------
1 1 1
Executing function
Executing function
Executing functionThe trick is to put the function inside a
scalar subquery - i.e. a select in the select.
Then subquery caching kicks in and the function only executes once:
select n, n, n
from (
select ( select f from dual ) n from dual
);
N N N
---------- ---------- ----------
1 1 1
Executing function
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment