cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PeeWee33
Frequent Visitor

Oracle connection failed in service, not in PowerBI desktop

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?

1 ACCEPTED SOLUTION
PeeWee33
Frequent Visitor

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

 

View solution in original post

2 REPLIES 2
PeeWee33
Frequent Visitor

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
Resolver I
Resolver I

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

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power BI Dev Camp Session 27

Ted's Dev Camp

This session walks through creating a new Azure AD B2C tenant and configuring it with user flows and custom policies.

Top Solution Authors
Top Kudoed Authors