Date problem in XI webi doc

[jaburke]

Hi,
I have a BO 5.1.7 doc that was migrated to XI. It is giving me an error when I refresh the data. The error says: "ORA-01843 - not a valid month".
However, when I take the sql and run it in sqlplus, the query returns rows.

Help. I'm using Oracle 9i and BO Enterprise XI.

Thanks!!
Jen

 

[blom0344]

Oracle default date format is dd-mon-yy (i.e. 01-JAN-05)

There is probably a mismatch between date formatting between Oracle and BO



Ties Blom
Information analyst

 

[jaburke]

Maybe we haven't updated the oracle format on our (new) 9i instance? We dont' have this proble running from oracle 8i (BO 5.1.7)
How would I ask my dba to check??

Thanks,
Jennifer

 

[blom0344]

AFAIK, the date format in ORACLE is a global setting. During a session you can manipulate it with the NLS_DATE_FORMAT command, but that would be something to do in SQL PLUS.

What is the setting in the ORACLE.sbo file (for format of input date)?



Ties Blom
Information analyst

 

[jaburke]

I'm sorry, I'm not familiar with the oracle.sbo file. Where can I go to check it?

Thanks!

 

[blom0344]

It is in the dataAccess folder that is somewhere within the root of where you installed Business Objects
(just perform a search on *.sbo)

Ties Blom
Information analyst

 

[jaburke]

Fantastic - I found the file.
What am I looking for? I search on the word date and no hits. (This is XI implementation)
I went and looked at the oracle.sbo on my current 5.1.7 implementation and I see the date stuff in it.

This is everything in my XI oracle.sbo for defaults:

<Defaults>
<Parameter Name="Family">Oracle</Parameter>
<Parameter Name="SQL External File">oracle</Parameter>
<Parameter Name="SQL Parameter File">oracle</Parameter>
<Parameter Name="Description File">oracle</Parameter>
<Parameter Name="Strategies File">oracle</Parameter>
<Parameter Name="Driver Level">31</Parameter>
<Parameter Name="Array Fetch Available">True</Parameter>
<Parameter Name="Array Bind Available">True</Parameter>
<Parameter Name="Query TimeOut Available">False</Parameter>
<Parameter Name="Binary Slice Size">32000</Parameter>
<Parameter Name="CharSet Table">oracle</Parameter>
</Defaults>

Thanks so much!!
Jen

 

[blom0344]

Jen,

This is beyond my experience as I have not yet worked with XI.
I hope Steve Krandel will sort this out.

Otherwise check the following (more active) community:

http://www.forumtopics.com

Ties Blom
Information analyst

 

[jaburke]

Thanks so much for your help thus far!!

Jen

 

[skrandel]

Sorry guys. I haven't used XI yet, either. But, I would call BO and ask them. This definitely sounds like an "InitDateFormat" or "InputDateFormat" problem.

Steve Krandel
Symantec

 

[jaburke]

Thanks guys. I did open a ticket with BO on Thursday. When I get a resolution, I'll post it as an FYI.

Thanks!

 

[jaburke]

Of course I ended up figuring out the problem before tech support could. Here's the deal. I had an object in the universe that was defined as a date. In the select of the object was a decode statement kind of like this:

decode(date_field,'20461231','YYYYMMDD',NULL,date_field)

When I parse this statment in 5.1.7 i get no errors. When I parsed in XI, I got an error. I had to change the statement to this:

decode(date_field,'20461231','YYYYMMDD',to_date(NULL),date_field)

Weird.....but that fixed the problem.