I am encountering a “Unhandled Exception” c0000005 error when I try to parse a object in Designer that is dividing two different objects that consist of nested decodes. The unusual thing is that the objects were parsing but now I will get the “Unhandled Exception” c0000005 error and the Designer application will close down. All of my other objects are working fine. Has anyone ever experience problems working with nested decodes? The nested decodes seems to be what all the problem objects have in common.
We are using BO version 5.1.5 on Oracle 9i
Thanks for any help!
I’ve been getting the same error today on a large object on oracle 9i, BO 5.1.2 whilst nesting several layers of case whens and decodes
The error was occurring:
when hitting the “Parse object” button in Designer
when running the SQL in free-hand SQL in Reporter
when running the unparsed object in Reporter Query Panel
when hitting the SQL button after picking the unparsed object in reporter query panel
I managed to fix it after much hair-pulling by putting a column alias in at the end of the select in designer which seems to sooth the brow of the parser - the object now parses OK & runs fine.
eg
SELECT
case when … decode … decode … else case when … else end … end “MY_NEATER_COLUMN_HEADER”
I’m also experiencing the infamous UEE during the parsing of a dimension object that consists of a CASE expression containing multiple WHEN clauses. Specifically, the object parses successfully with up to 15 WHEN clauses, but I get the UEE with 16 and beyond. It’s my understanding that you can have up to 127 WHEN clauses in a CASE expression, so I don’t understand why this is happening. Has anyone encountered the same limitation? If so, what was your solution? Is this limitation a known bug with Designer? Thanks.
I don’t know that the 15 When clauses is the limit specifically, but it does have to do with the length and complexity of the select clause itself. It crashes when you parse the object individually, right? As long as it parses when you do check integrity on the universe objects, the object is fine. The moral to the story is “if it crashes when you parse it, then don’t parse it” smile.
Yes, it crashes when parsed individually (i.e., from the Edit Properties dialog box). I ran the “Integrity Check” including the “Parse Objects” as you suggested and everything came back with an “Ok.” 8) The object also worked correctly when I used it in a report. 8) Finally, I entered the Select statement in Toad and it executed correctly. 8) So, I guess the moral of the story is that I shouldn’t assume there is a problem with my CASE statement just because the infamous UEE appears during an individual parse. Thanks so much Dwayne for you suggestion.
The more complex my CASE statements become (i.e., the more WHEN clauses or nested CASE statements I use), the more frequent the UEE instances become, even to the point where the object causes multiple UEEs in Reporter followed by a crash. Again, when I run the Select statement in TOAD, it executes normally. Out of curiosity, I tried a DECODE statement in the place of the CASE and the darn thing parsed individually without a problem and worked just fine in Reporter.
I logged a case with BO yesterday. I’ll keep everyone updated on what they say.
Thanks John. We have experienced the same behavior here - decodes never gave us any problems, whereas the more efficient CASE statements seem to give Business Objects fits. We too can execute these statements with no problems using SQL*Plus, TOAD or similar query tools.
Also, we experience this behavior against both Oracle and DB2.
Okay…I have an answer, but I need to be careful what I say because of those copyright laws.
THIS IS A KNOWN ORACLE BUG (Oracle Bug #2510357). The problem has also been documented in BO’s Knowledge Base under Resolution Entry #13669 (with a title something like GPF/Unhandled Exception when parsing or running query using object defined as CASE or DECODE against Oracle 8.1.7 or higher). The resolution number was provided by the BO technical representative working my case. Good luck finding the resolution in the Knowledge Base because I’ve been unsuccessful so far (I am working with BO to try and figure out what the problem is). The representative did email me a copy of the resolution, but I can’t share it with anyone.
The bug involves query applications and the version of OCI they use. Applications that use version 8 of OCI (e.g., TOAD, SQL Plus) don’t have the problem and applications that use version 7 (e.g., Business Objects) do. For some reason version 7 OCI seems to have problems handling CASE or DECODE statements that are longer than a certain number of characters.
Hmm, I had no joy using the id you gave. I think you have posted enough of a flavour of the root of the problem to help anyone who encounters this in the future.
-Just got word from BO tech. support that the BO resolution I referenced is not available to registered users of technical support (i.e., the resolution is only available to BO employees).
I told the BO rep. that I thought this one was significant enough that it should be made available to registered users. She said she would forward my request to her management.
Oh right got you, they keep some to themselves because they don’t think anyone else is likely to get those problems. So some poor user encounters a problem, uses his tech support account to search for a resolution, finds nothing… Hello! Is it just me or does this approach seem slightly lacking?
When Lee Chen was here from BO he logged into techsupport from my desk. What he saw was very different from what we saw. He had access to way more resolutions. Even with the known issues, he sometimes had more potential solutions than I was able to see. So, it wasn’t necessarily that the more obscure problems were hidden, even resolutions for common problems were more abundant. I agree Nick. Kinda strange.
Okay…the final word from BO is that this problem is considered an Oracle bug. Because the resolution entry contains information published by Oracle, BO cannot publish the resolution using their Online Customer Support site.
I searched for this today as I was having the UEE on an newly developed universe using a new ODBC connection with ‘disconnect after each transaction’. What I did was to Refresh the universe in BO full client from Tool, Universe, Refresh and it solved the problem at query time, but then when I closed the BO doc I got the UEE again.
Just FYI
Thanks for the discussion
I had the same problem. here’s how I got around it, I created the nested decode in designer but didn’t parse. I went to reporter parsed the object in the sql dialogue. It didn’t fail or result in an UEE. After parsing in reporter I no longer got the UEE error in designer. I know this sounds like a silly work around but it worked for me…
I can’t remember if I did this for every object with a nested decode…
(BOB member since 2002-08-15)
It’s my understanding that you can have up to 127 WHEN clauses in a CASE expression, so I don’t understand why this is happening. Has anyone encountered the same limitation? If so, what was your solution? Is this limitation a known bug with Designer? Thanks.
(BOB member since 2002-10-01)
(BOB member since 2002-06-20)
(BOB member since 2002-08-15)