a snippet to demonstrate that sql -> plsql -> sql calls may break statement level consistency while querying actively updated tables.

session_1.log

SQL> 
SQL> whenever sqlerror continue
SQL> 
SQL> drop table test_1 purge;

Table dropped.

Elapsed: 00:00:00.04
SQL> drop table test_2 purge;

Table dropped.

Elapsed: 00:00:00.04
SQL> 
SQL> drop function session_exists;

Function dropped.

Elapsed: 00:00:00.01
SQL> 
SQL> whenever sqlerror exit failure
SQL> 
SQL> create table test_1 as select rownum id from dual connect by rownum <= 5;

Table created.

Elapsed: 00:00:00.03
SQL> create table test_2 as select 0 id from dual;

Table created.

Elapsed: 00:00:00.02
SQL> 
SQL> create function session_exists(p_action v$session.action%type)
  2      return boolean
  3      as
  4  begin
  5      for rec in (select sid from v$session where action = p_action) loop
  6          return true;
  7      end loop;
  8      return false;
  9  end session_exists;
 10  /

Function created.

Elapsed: 00:00:00.01
SQL> 
SQL> exec dbms_application_info.set_action('session_1');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL> 
SQL> begin
  2      -- wait for session_2 to start
  3      while not session_exists('session_2') loop
  4          dbms_lock.sleep(1);
  5      end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.06
SQL> 
SQL> -- session_2 starts incrementing test_2.id at this point!
SQL> 
SQL> set echo on
SQL> 
SQL> -- test case 1: one test_2 query, cross join
SQL> -- expectation: same t2_id for every t1 row
SQL> with
  2      function sleep_1_sec(id int) return int as
  3      begin
  4          -- return the argument after waiting for 1 sec
  5          sys.dbms_lock.sleep(1);
  6          return id;
  7      end sleep_1_sec;
  8  select
  9      t1.id t1_id,
 10      sleep_1_sec(t2.id) t2_id
 11  from
 12      test_1 t1,
 13      test_2 t2
 14  /

     T1_ID      T2_ID
---------- ----------
         1       1861
         2       1861
         3       1861
         4       1861
         5       1861

5 rows selected.

Elapsed: 00:00:05.20
SQL> 
SQL> -- test case 2: multiple test_2 queries from function
SQL> -- expectation: different t2_id for every t1 row
SQL> with
  2      function get_test_2_id return int as
  3      begin
  4          -- return test_2.id after waiting for 1 second
  5          -- pretend this is a long-running query
  6          sys.dbms_lock.sleep(1);
  7          for rec in (select id from test_2) loop
  8              return rec.id;
  9          end loop;
 10      end;
 11  select
 12      id t1_id,
 13      get_test_2_id() t2_id
 14  from test_1
 15  /

     T1_ID      T2_ID
---------- ----------
         1      26326
         2      30586
         3      34760
         4      38894
         5      42892

5 rows selected.

Elapsed: 00:00:05.18
SQL> 
SQL> -- test case 3: same query as in test 2,
SQL> -- BUT in read-only transaction
SQL> -- expectation: same t2_id for every t1 row
SQL> set transaction read only;

Transaction set.

Elapsed: 00:00:00.00
SQL> 
SQL> with
  2      function get_test_2_id return int as
  3      begin
  4          sys.dbms_lock.sleep(1);
  5          for rec in (select id from test_2) loop
  6              return rec.id;
  7          end loop;
  8      end;
  9  select
 10      id t1_id,
 11      get_test_2_id() t2_id
 12  from test_1
 13  /

     T1_ID      T2_ID
---------- ----------
         1      42896
         2      42896
         3      42896
         4      42896
         5      42896

5 rows selected.

Elapsed: 00:00:05.21
SQL> 
SQL> exit

session_1.sql

set echo on
set time off

spool session_1.log

whenever sqlerror continue 

drop table test_1 purge;
drop table test_2 purge;

drop function session_exists;

whenever sqlerror exit failure

create table test_1 as select rownum id from dual connect by rownum <= 5;
create table test_2 as select 0 id from dual;

create function session_exists(p_action v$session.action%type)
    return boolean
    as
begin
    for rec in (select sid from v$session where action = p_action) loop
        return true;
    end loop;
    return false;
end session_exists;
/

exec dbms_application_info.set_action('session_1');

begin
    -- wait for session_2 to start
    while not session_exists('session_2') loop
        dbms_lock.sleep(1);
    end loop;
end;
/

-- session_2 starts incrementing test_2.id at this point!

set echo on

-- test case 1: one test_2 query, cross join
-- expectation: same t2_id for every t1 row
with
    function sleep_1_sec(id int) return int as
    begin
        -- return the argument after waiting for 1 sec
        sys.dbms_lock.sleep(1);
        return id;
    end sleep_1_sec;
select
    t1.id t1_id,
    sleep_1_sec(t2.id) t2_id
from
    test_1 t1,
    test_2 t2
/

-- test case 2: multiple test_2 queries from function 
-- expectation: different t2_id for every t1 row
with
    function get_test_2_id return int as
    begin
        -- return test_2.id after waiting for 1 second
        -- pretend this is a long-running query
        sys.dbms_lock.sleep(1);
        for rec in (select id from test_2) loop
            return rec.id;
        end loop;
    end;
select
    id t1_id,
    get_test_2_id() t2_id
from test_1
/

-- test case 3: same query as in test 2, 
-- BUT in read-only transaction
-- expectation: same t2_id for every t1 row
set transaction read only;

with
    function get_test_2_id return int as
    begin
        sys.dbms_lock.sleep(1);
        for rec in (select id from test_2) loop
            return rec.id;
        end loop;
    end;
select
    id t1_id,
    get_test_2_id() t2_id
from test_1
/

exit

session_2.sql

set echo on 

spool session_2.log

exec dbms_application_info.set_action('session_2')

begin
    -- keep updating test_2 like crazy while 
    -- session_1 queries are still running
    while session_exists('session_1') loop
        update test_2 set id = id + 1;
        commit;        
    end loop;
end;
/

exit