Cognos Analytics

 View Only
Expand all | Collapse all

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

  • 1.  Burst Report Runs then Fails on Subsequent Run w/ UDA-SQL-0107 and ORA-12801 Message

    Posted Tue May 23, 2023 03:10 PM

    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 McIlravey
    ------------------------------


  • 2.  RE: Burst Report Runs then Fails on Subsequent Run w/ UDA-SQL-0107 and ORA-12801 Message

    IBM Champion
    Posted Wed May 24, 2023 05:51 AM

    The "ORA-01722: invalid number" error makes me think you should start with checking your data. Are you do any data casting (implicitly or explicitly) from any type of string to any type of number? Possibly in a join or a filter? What might be happening is that there's a value that is an invalid number that in some cases isn't returned because of of filters, and then in other cases isn't caught by the filter yet.

    You can use VALIDATE_CONVERSION(<field> AS NUMBER) = 0 as your filter when testing specific fields.  



    ------------------------------
    Paul Mendelson
    ------------------------------



  • 3.  RE: Burst Report Runs then Fails on Subsequent Run w/ UDA-SQL-0107 and ORA-12801 Message

    Posted Wed May 24, 2023 04:44 PM

    Thanks for the feedback Paul...
    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.

    As always, thanks again for your help,
    Adam.   



    ------------------------------
    Adam McIlravey
    ------------------------------