APEX substitution string disappearing

Question

I'm running APEX 5.1 on ORACLE 12.1.

I have ca. 80 APEX similar applications running on the same database - each application is using the same substitution string :CLIENT_ID with its own value (storing a code name of the application). :CLIENT_ID is used through the "V" function: V('CLIENT_ID') all over the applications calling a shared framework of views and packages on the database.

Out of the sudden in one of the applications, one of the Interactive Reports fails with the exception that the :CLIENT_ID is not set. All other pages or similar pages in other applications work perfectly fine.

• Surprisingly, if I add a natural join with 1 row from dual it starts working fine! But only if it is natural join and only if I select a value of a function reading some other APEX substitution variable.
• If I change it by adding "natural join (select 1 from dual)" it keeps failing.
• recompiling the view that uses :CLIENT_ID does not help.
• running SQL forming a failing view does not help.
• some changes to the SQL forming a failing view help (but give wrong data so cannot be a solution)
• DBMS_RESULT_CACHE.FLUSH does not help
• ALTER SYSTEM FLUSH BUFFER_CACHE does not help
• ALTER SYSTEM FLUSH SHARED_POOL does not help

Can anyone give me any hint (except bouncing the database) what I could do to investigate more?

Answer 1

This is a consequence of the explain plan changing over time. The root cause of the problem is described here: When "PARTITION LIST SUBQUERY" is in the execution plan something (a bug?) de-instantiates the package