Thus, if you have a statement like "SELECT col_1, my_function(col_2) FROM ...", the function "my_function" is executed from every row that is returned from the query. Oracle guarantees Read Consistency at the Statement Level. However, insofar as the Function is executed, each execution of the Function is a separate SQL statement executed against the database. If underlying data has been changed in transaction from a different database session, the Function may start returning different values mid-way in a result set.
Here's a demonstration of how a Function being used for a lookup returns different values for the same lookup, within a single SQL statement.
SQL> drop table my_obj_types_lookup purge;
SQL> drop table obj_names_and_descr purge;
SQL> -- create the Lookups table
SQL> create table my_obj_types_lookup
2 (obj_type varchar2(30) not null, obj_descr varchar2(50) not null)
SQL> insert into my_obj_types_lookup select distinct object_type, 'Is an ' || object_type from dba_objects;
42 rows created.
SQL> -- REM Create a function that does Lookups
SQL> create or replace function lookup_obj_descr (obj_type_in varchar2)
2 return varchar2
4 obj_type_ret varchar2(50);
6 select obj_descr into obj_type_ret from my_obj_types_lookup where obj_type = obj_type_in;
7 return obj_type_ret;
8 exception when others then return 'Unknown object type';
SQL> -- REM create the target table
SQL> create table obj_names_and_descr
2 (obj_owner varchar2(30) not null, obj_name varchar2(128) not null, obj_type varchar2(30) not null, obj_descr varchar2(60) not null);
SQL> pause PRESS ENTER TO PROCEED
PRESS ENTER TO PROCEED
SQL> -- Now we populate OBJ_NAMES_AND_DESCR using our Lookup Function !
SQL> insert into obj_names_and_descr
2 select owner, object_name, object_type, lookup_obj_descr (object_type)
3 from dba_objects
4 order by owner
50639 rows created.
SQL> -- REM Let's verify the count of different types of objects
SQL> select obj_type, obj_descr, count(*)
2 from obj_names_and_descr
3 where obj_type like 'TAB%'
4 group by obj_type, obj_descr
5 order by 1,2
OBJ_TYPE OBJ_DESCR COUNT(*)
------------------------------ ------------------------------------------------------------ ----------
TABLE Is an TABLE 897
TABLE OOPS ! 759
TABLE PARTITION Is an TABLE PARTITION 128
SQL> -- REM REM REM #!#!
SQL> -- REM Hey ! Where did the "OOPS !" rows come from ? My INSERT INTO ... SELECT should have given my Read Consistency
SQL> -- REM Therefore, every TABLE should have the same OBJ_DESCR !
Surely, the "insert into obj_names_and_descr" should have "seen" a Read Consistent image of data ? Yes. The image from the dba_objects view would have been consistent. However, the lookup function "lookup_obj_descr" was executed once against "my_obj_types_lookup" for *each* row from the query against dba_objects. Each execution of the function was independent of the other executions. It so happens that the underlying table "my_obj_types_lookup" was updated by someone else running :
SQL> update my_obj_types_lookup set obj_descr = 'OOPS !' where obj_type = 'TABLE';
1 row updated.
mid-way through the INSERT ... SELECT operation.
Therefore, although some rows that were returned from dba_objects saw the obj_descr as "Is an TABLE" for obj_type = 'TABLE' , rows that were still being fetched from dba_objects after the other session committed his update to 'OOPS !', now saw the new value 'OOPS !' as being the description.
Therefore, the SELECT portion of the INSERT actually returned different values for obj_descr when using the Function !