Alternative for a cursor

Question

I have a function that with each invocation changes its value. I would like to use this value more than once in a query, but I don't want to execute it more than once because I want the value from the first invocation. I have tried:

SELECT RESULT value1,
       RESULT value2,
       RESULT value3
  FROM (SELECT function_invocation() RESULT
          FROM dual);

But each VALUE column gives me different value which means that the function was invoked more than once.

An alternative would be to write a cursor but I was wondering if it's possible with pure SQL.

Answer 1

There are a few tricks to prevent Oracle from unnecessarily re-executing functions. This topic is difficult because 99.9% of the time we depend on Oracle to automatically re-write queries to run optimally. Stopping those optimizations should not be a common task.

In theory, there is no way to guarantee the order of operations of a declarative SQL statement. In practice, there are two simple techniques that can help prevent functions from being re-run: scalar subquery caching and ROWNUM.

First, let me try to reproduce the problem. A single value reference returns three different numbers.

create or replace function function_invocation return number is
begin
    return dbms_random.value;
end;
/

SELECT RESULT value1,
       RESULT value2,
       RESULT value3
  FROM (SELECT function_invocation() RESULT
          FROM dual);

VALUE1   VALUE2   VALUE3
------   ------   ------
0.3089   0.7103   0.2885

Re-writing the query to use a scalar subquery seems unnecessary, but this form enables Oracle to use scalar subquery caching, an optimization technique that Oracle uses to avoid re-running code. Now the three columns return the same value.

select result value1, result value1, result value1
from
(
    select (select function_invocation() from dual) result from dual
);

VALUE1   VALUE2   VALUE3
------   ------   ------
0.2450   0.2450   0.2450

Alternatively, we can prevent optimization transformations by adding a ROWNUM pseudo-column:

SELECT RESULT value1,
       RESULT value2,
       RESULT value3
  FROM (SELECT function_invocation() RESULT, rownum
          FROM dual);

VALUE1   VALUE2   VALUE3
------   ------   ------
0.1678   0.1678   0.1678

These techniques work well in practice for getting the results to look the right way. However, it's possible that the function will be secretly run an additional time before the execution. That extra run is for parsing and caching, and won't affect the results. But if you have a function with side-effects that can only be run once, there are some extra hoops you must jump through.