You may know that whenever you call PL/SQL functions from within SQL query, each query in the function is consistent to the SCN of its start and not to the SCN of parent query.
Simple example:
create table test as
select level a, level b from dual connect by level<=10;
create or replace function f1(a int) return int as
res int;
begin
select b into res
from test t
where t.a=f1.a;
dbms_lock.sleep(1);
return res;
end;
/
As you can see we created a simple PL/SQL function that returns the result of the query select b from test where a=:input_var
But lets check what does it return if another session changes data in the table:
-- session 2:
begin
for i in 1..30 loop
update test set b=b+1;
commit;
dbms_lock.sleep(1);
end loop;
end;
/
-- session 1:
SQL> select t.*, f1(a) func from test t;
A B FUNC
---------- ---------- ----------
1 1 1
2 2 3
3 3 5
4 4 7
5 5 9
6 6 11
7 7 13
8 8 15
9 9 17
10 10 19
10 rows selected.
As you can see we got inconsistent results in the column FUNC, but we can easily fix it using OPERATORs:
CREATE OPERATOR f1_op
BINDING (INT)
RETURN INT
USING F1;
Lets revert changes back and check our query with new operator now:
--session 1:
SQL> update test set b=a;
10 rows updated.
SQL> commit;
Commit complete.
-- session 2:
begin
for i in 1..30 loop
update test set b=b+1;
commit;
dbms_lock.sleep(1);
end loop;
end;
/
-- session 1:
SQL> select t.*, f1(a) func, f1_op(a) op from test t;
A B FUNC OP
---------- ---------- ---------- ----------
1 2 2 2
2 3 5 3
3 4 8 4
4 5 11 5
5 6 14 6
6 7 17 7
7 8 20 8
8 9 23 9
9 10 26 10
10 11 29 11
10 rows selected.
As you can see, all values in the column OP are equal to the values of B, while, in turn, function F1 returns inconsistent values.
