EXECUTE IMMEDIATE sometimes doesn't retrieve any information

User_HIE3G

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?

Paulzip

Are you suppressing exceptions? E.g.

when OTHERS then

or similar?

User_HIE3G

Hi Paulzip,

https://community.oracle.com/tech/developers/discussion/comment/16778288#Comment_16778288

Yes, we're suppresing exceptions with "when others then", even we are logging in a error table in case of any error. In the past we aren't supressing that exceptions.

Also i put in a log the results in this issue, and it throws 0 results.

Paulzip

Show the code and it might be obvious, but my hunch is an exception is getting raised, suppressed and not getting logged for some reason.