SOLVED: Burst Report Runs then Fails on Subsequent Run w/ UDA-SQL-0107 ORA-12801

[adam_mc]

CA 11.1.7 - Oracle

We are experiencing an issue where a burst report runs successfully the first time, but then all subsequent runs fail (for some period of time).
If we then go in and change the report query by adding a "garbage" not in condition (such that the explain plan is changed), the report runs successfully again.
However, subsequent runs of this now fail.

The message is something similar to below, but always seems to include "Parallel query server..."

• RQP-DEF-0177 An error occurred while performing operation 'sqlOpenResult' status='-9'. UDA-SQL-0107 A general exception has occurred during the operation "open result". ORA-12801: error signaled in parallel query server P00J ORA-01722: invalid number

If the report is run so as not to burst, the report runs successfully every time.

We are wondering if this is an Oracle caching issue or something related to that?
Why else would it run successfully and then subsequently fail?

Any thoughts would be gratefully appreciated.
Thanks in advance,
Adam.

[adam_mc]

We were able to finally resolve the issue and have come to the conclusion that this was likely Cognos caching rather than Oracle caching.

Both the Report Query and Burst Query originated as User Defined SQL, but then contained Oracle functions within some of the passed thru calculations
For example: to_char and nvl

Our belief is that these worked on the first run as everything was done via the database, but in subsequent runs it was was using Cognos cached results and the Oracle functions (particularly the to_char causing the invalid number) were causing the failure.

Therefore, we removed the Oracle calculation functions from the Cognos queries and then pushed those calculations back to the User Defined SQL's and changed the SQL Syntax from Native to Pass-Through.

Thanks,
Adam.