Oracle connection failed in service, not in PowerBI desktop

[PeeWee33]

I have a .pbix file with a connection to Oracle. In the PBIX there is an Oracle SQL statement to retrieve data. This al works fine when I get data and refresh as long as I do this in PowerBI desktop.

In PowerBI service I am not able to refresh my data. I have a working gateway set up that works fine with other datasets. When I try to refresh this dataset I get an Oracle error:  

{"type":1,"value":"-2147467259"}},{"code":"DM_ErrorDetailNameCode_UnderlyingErrorMessage","detail":{"type":1,"value":"Oracle: ORA-01843: not a valid month"}},

Anyone got an Idea why this works in the desktop and not in the service?

[PeeWee33 · Accepted solution]

Thanks Matt

I finally gott it working.  I had a working function to convert a datetime to a date. 

to_date(to_char(mytimecolumn, 'DD-MM-YYYY'))

I changed it to trunc(mytimecolumn)  and now I works just fine. I've got a feeling it has something to do with the locale but I do not bother to check if that is true or not

[m13eam]

Hi - this is a slight guess, but I wonder if this is to do with dates implicitly trying to convert from text to the wrong date format. For example, in the SQL, does it have something like the below?

MyDateField > '20/9/22'

My hunch is that is considering that to be MM/DD/YY - i.e. the 9th of the 20th month, 2022 - and throwing an error.

If so, changing such references to the below will provide explicit conversion instructions that should work:

MyDateField > TO_DATE('20/9/22','DD/MM/YY')

Thanks,

Matt