PL/SQL Functions in the WITH Clause and Read (In)Consistency

A question was raised in Martin Widlake’s session this morning in OUG Ireland 2016. The (excellent) session was about the performance of calling PL/SQL functions from SQL, and Martin scared the audience (or at least tried to scare…) with the fact that read consistency is not kept (with respect to the base SQL statement) when the PL/SQL functions are called from that SQL statement.
The question that was raised was whether this is also true when the PL/SQL functions are embedded in the WITH clause – a new 12c feature about which you can read in detail in Write Less with More – Part 8.

Here is a small example that shows that read consistency is not kept in this case as well.

create table t (x number);

with function f return number is
    c number;
  begin
    select count(*) into c from t;
    dbms_lock.sleep(10);
    return c;
  end f;
select f from dual
union all
select f from dual
/

Now, right after starting executing the previous query, from another session we do the following:

insert into t (x) values (1);
commit;

and the result that we get from the query is:

0
1

<