A PL/SQL function that retrieve data from a plain SQL query using EXECUTE IMMEDIATE, DBMS_SQL package, Ref Cursor, or also from a ADF View Object it is not returning any rows sometimes.
We've already tried the following things:
Checked that the plain query returns rows using SQL Developer.
We Separated the session by using a JOB that calls the function that retrieve the information (DBMS_JOB).
Using NO_RESULT_CACHE hint with ALTER SESSION SET RESULT_CACHE_MODE = MANUAL (without any memory flushing)
All queries are for a specific table but using different WHERE clause.
Any idea or known behaviour about our problem?