SQLs in Functions : Each Execution is Independent

Functions (Stored PLSQL code that return a single value at each call) are good for calculations, validation against business rules etc. But when you use them for lookups, you must watch out for underlying data being changed !

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;

Table dropped.

SQL> drop table obj_names_and_descr purge;

Table dropped.

SQL>
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)
3  /

Table created.

SQL> insert into my_obj_types_lookup select distinct object_type, 'Is an ' || object_type  from dba_objects;

42 rows created.

SQL> commit;

Commit complete.

SQL>
SQL> -- REM Create a function that does Lookups
SQL> create or replace function lookup_obj_descr (obj_type_in  varchar2)
2  return varchar2
3  as
4  obj_type_ret  varchar2(50);
5  begin
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';
9  end;
10  /

Function created.

SQL>
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);

Table created.

SQL>
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
5  /

50639 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
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
6  /

OBJ_TYPE                       OBJ_DESCR                                                      COUNT(*)
------------------------------ ------------------------------------------------------------ ----------
TABLE                          Is an TABLE                                                         897
TABLE                          OOPS !                                                              759
TABLE PARTITION                Is an TABLE PARTITION                                               128

SQL>
SQL>
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 !
SQL> 

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.

SQL> commit;

Commit complete.

SQL> 

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 !

.
.
.