ORACLE 19c, cursor expression & AOP
Having issues after Oracle 19c upgrade with AOP reports in APEX?
Oracle 12 and Oracle 19c gave different results running a query
Something that happened to us recently that you should be aware of if you recently upgraded to Oracle 19c and if you are using cursor expressions like ones common in APEX Office Print.
This is a query that has been running in production for a while (Oracle 12):
select id as "id",
name as "name",
rap2.i1 as "i1",
rap2.r1 as "r1",
rap2.r2 as "r2",
rap2.i3 as "i3",
rap2.r3 as "r3",
rap2.i4 as "i4",
rap2.r4 as "r4",
rap2.trad_id as "trad_id",
rap2.zone as "zone",
rap2.wage_code as "wage_code",
rap2.jrny_rate as "jrny_rate"
from v_program_request_access rap2
where rap2.id = rap1.id
) as "det"
from v_program_request_access rap1
where rap1.id = 28820
group by id, name;
After the upgrade to Oracle 19c our clients reported a problem with the above report saying why it is not returning data anymore when we know there is data available?
After few hours debugging and double checking what is going on there was nothing obvious.
For some unknown reason query in 12c on same data set was returning
where the same query run on Oracle 19 returned something quite different:
Notice how 19c returned all null values in cursor expression.
Then Skillbuilders Database expert came onto a stage and actually found more interesting things.
Bottom line is this was a root cause of our AOP report not return data back into user PDFs as Oracle 19c didn't return data from cursor expression.
It seems to be a bug in Oracle 19c. Ticket is raised with Oracle to help us sort this issue so keep tuned for more info.