Solved: Oracle connection failed in service, not in PowerB...

link管理

链接快照平台

输入网页链接，自动生成快照
标签化管理网页链接

Be one of the first to start using Fabric Databases.

Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started

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?

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

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

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

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

Learn More