Questions about Scheduled Cache Purging in OAS

Chad Williams

Hello,

At some point, we set up an agent/analysis in OBIEE 11/12 that would run each day and clear the case by running the "call SAPurgeAllCache()" command through this process. The process is well documented here:

Clear OAC cache automatically. One of the requirements that customers… | by Eloi Lopes | Medium

We have since upgraded from OBIEE 12c to OAS 2023 and now reside on OAS 2025 (RHEL9), and have noticed recently that this process no longer is working as it did originally. No special plans were made to account for this during the upgrades, so I'm wondering if the above option is still even a possibility as the Direct SQL in the analysis no longer seems to return a result when running "call SAPurgeAllCache()". We get the following error:

Is someone able to confirm if this is the expected result now when running this process, or should it still work?

Considering that it very likely does not, and have started to look for other ways to fulfill the requirement to reset our cache at regular scheduled intervals. I did find this documentation, which looks promising, but do not want to start down this road until we've exhausted all other options.

Scheduled Cache Purging in OAC — Oracle Analytics

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=480422381680417&id=2956513.1&_afrWindowMode=0&_adf.ctrl-state=18wuq2dj51_102

How are others approaching this issue? Any other info is more than welcome.

Regards,

Chad

Chad Williams

I also noticed we have this in our odbc.ini file. I don't know if anything here needs to updated or not.

Gianni Ceresa

Hi,

Is it really not working?

The error you have is not really an error, it is just telling you that your "query" wasn't expected to return any column, while now it does return 2 columns.

If you run it in the Issue SQL page, you can see the 2 columns:

Somehow it looks like your old agent was executing a query expecting no columns as a result. Did you try just updating that one to now make it return the 2 columns? You don't really care about the value returned, you just need to match the metadata of your analysis running the query with the structure of the result of the execution.

Chad Williams

https://community.oracle.com/products/oracleanalytics/discussion/comment/69322#Comment_69322

Yea, I was wondering the same thing. Currently in our analysis we see this:

I'm not sure it likes the space in the "Cache Purge" connection pool name, so I shortened it to "CachePurge". When I do so and revalidate, the columns you see there go away. Hence, the expected 0 and got 2 error above.