Read consistency of custom functions in Oracle PL/SQL

Read consistency ensures that the query results are consistent with the data at the time when the query is initiated, regardless of whether other transactions have changed the data during the query.

Assume that we have the following function to calculate the total salary of the department based on the department id:

1. Create or replace function sum2 (p_deptno in number)
2. Return number is -- PRAGMA AUTONOMOUS_TRANSACTION;
3. Rochelle ret number;
4. BEGIN
5. Dbms_lock.sleep (5 );
6. -- DBMS_BACKUP_RESTORE.SLEEP (5 );
7. Dbms_output.put_line (systimestamp );
8. Select sum (sal)
9. INTO l_ret
10. FROM emp
11. WHERE deptno = p_deptno;
12. RETURN l_ret;
13. END sum2;

Then we open a session (s1) and execute the following query (q1 ):

1. SELECT deptno
2. , SUM (sal)
3. , Sum2 (deptno)
4. , Systimestamp
5. FROM emp
6. Group by deptno;

During q1 execution, we opened a new session (s2), executed the update command, and submitted:

1. SQL> update empSetSal = sal + 1;
3. 14 rows updated
5. SQL> commit;
7. Commit complete
9. SQL>

Return to s1 and check the q1 execution result:

1. SQL> SELECT deptno
2. 2, SUM (sal)
3. 3, sum2 (deptno)
4. 4, systimestamp
5. 5 FROM emp
6. 6 group by deptno;
8. Deptno sum (SAL) SUM2 (DEPTNO) incluimestamp
9. ------------------------------------------------------------------------------------------------------------
10. 30 9400 9406 24-JUN-11 08.31.19.722000 PM +
11. 20 10875 10880 24-JUN-11 08.31.19.722000 PM +
12. 10 8750 8753 24-JUN-11 08.31.19.722000 PM +
14. SQL>

We found that the results of sum (sal) and sum2 (deptno) are inconsistent.

When we apply a user-defined function to an SQL statement, which takes a long time to execute and some other dml changes some tables in the SQL statement during this time, this inconsistency will be reflected.

Set transaction read only can be used to avoid such inconsistency. For details, refer to my article below:

Oracle PL/SQL: SET TRANSACTION READ ONLY (TRANSACTION isolation)