I was hoping that if inline “with” functions are in the query, so their results will be consistent with it (as operators), but unfortunately such functions returns also inconsistent results as standalone pl/sql functions.
SQL> create table t as select 1 a from dual;
Table created.
SQL> declare
2 j binary_integer;
3 begin
4 dbms_job.submit( j
5 ,'begin
6 for i in 1..10 loop
7 dbms_lock.sleep(1);
8 update t set a=a+1;
9 commit;
10 end loop;
11 end;'
12 );
13 commit;
14 end;
15 /
PL/SQL procedure successfully completed.
SQL> with
2 function f return int is
3 res int;
4 begin
5 dbms_lock.sleep(1);
6 select a into res from t;
7 return res;
8 end;
9 select
10 f
11 from dual
12 connect by level<=10;
13 /
F
----------
1
1
1
2
3
4
5
6
7
8
10 rows selected.
Interesting: Jonathan Lewis wrote that inline “deterministic” functions doesn’t use caching mechanism as standalone deterministic functions.