I can not provide any code, so I try to be as precise as I can explaining how code works.
There is a process: some inserts, some updates etc. etc..
At some point there is a dbms_sql.execute procedure in use.
In this execute there is a statement which contains:
SELECT * from table(fun_get_data)
returning global type
CREATE OR REPLACE TYPE t_data_tab AS table of t__data_rec;
In function fun_get_data there is a "cache tool" - when executing for the 1st time, loading data to cache, then returns it every other time.
Problem is, that when dbms_sql.execute is in fire - it not sees uncommited data.
I even put a session id in log- everything works in same session and #serial.
Am i missing something? Is dbms_sql works as an autonomous transaction?
Edit: I did some deeper digging. Turns out, when oracle executing dynamic statement and it looks like
with data_ (select * from table(fun_get_data))
Select ...
from data_ d1
...
it not sees uncommited data, but when i modified SQL to this:
select ...
from (select * from table(fun_get_data))
everything is ok.
Any idea?
EDIT2: some deeper digging. It seems that
with data_ (select * from table(fun_get_data))
Select ...
from data_ d1
left join data_ d2
on d1.id = d2.id
...
if any kind of join appear in query(left, simple join or data_ d1, data_ d2 where), then uncommited data not exists. Without left join works... Dark magic truly..
