PBI Refresh (OLE DB or ODBC error: [DataSource.Error] Oracle: ORA-12514: TNS:listener does not curre

[Noobie]

Hi all,

Issue: PowerBI Refresh button needs to be clicked twice to finish refreshing.

I've been battling with this issue PowerBI Refresh button issue since Jan 2024. None of us had a clue as to why but all of the support team seemed to suggest it is application issue.

FYI : I connect to 3 Oracle database tables for data extraction into PowerBI Desktop for dashboard development and I publish the dashboard onto PBI Server manually. I am using a working TNS file everyone else is using and it is in the right folder and location. Edit Environment Variable done.

In Dec'2023,

My employer upgraded some server stuff, I started to have issue with using the Refresh button on tool bar to refresh my database tables. IT support had to run below script for us in order to get it to work.

"C:\Apps\Oracle64\12.2.0\client\odp.net\bin\4\OraProvCfg.exe" /action:gac /providerpath:"C:\Apps\Oracle64\12.2.0\client\odp.net\bin\4\Oracle.DataAccess.dll"

"C:\Apps\Oracle64\12.2.0\client\odp.net\bin\4\OraProvCfg.exe" /action:config /force /product:odp /frameworkversion:v4.0.30319 /providerpath:"C:\Apps\Oracle64\12.2.0\client\odp.net\bin\4\Oracle.DataAccess.dll"

Since Jan 2024,

We were required to upgrade PowerBI from version Sep 2023 to Version: 2.124.1052.0 64-bit (December 2023), I experienced the same issue again and no one was available to help me run the above script and someone suggested to install Oracle 12c client 12.2.0.1.0(x64) ODAC as it solved his problem. So I did. But turned out it did not help at all.  

But I discovered that I could still refresh database tables one by one by right clicking on the tables on the right hand side of PBI.

Last week I had a bubble moment and un-installed Oracle 12c client 12.2.0.1.0(x64) ODAC and got IT to re-ran the above script, and I managed to get my PowerBI to refresh but PBI will only fresh on second try, first attempt of refresh would always fail and I have to hit the Refresh button twice. The error message from first attempt reads 

"OLE DB or ODBC error: [DataSource.Error] Oracle: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor."

Would anyone be able to offer any tips? 

Much appreciated for any help.

[Noobie]

@Anonymous  thank you for your input.  I have not changed any settings and everyone else is using the same settings and installation and environment variables is pointing to the right folder too. I'm just focusing on app itself see if I can somehow resolve it. But for now, I just have to hit the refresh button twice. 

Tech support, cloud platform / PowerBI owner and Oracle DBA in my company all think it's not their issue or out of their scope of work because I can still connect to Oracle database based on their test results. . 

The only place that I may be able to find help is Microsoft or community.

Thanks

Cheers

Noobie

[Anonymous]

Hi, @Noobie 

Make sure that the Oracle listener is properly configured to recognize the service name defined in the file. You can do this by inspecting the file on the Oracle database server and making sure that it contains entries for the service that Power BI is trying to connect to. More details on configuring listeners.

Since you mentioned that the TNS files are in the correct folder and location, double-check that the environment variables are correctly pointing to the directory that contains your files. This ensures that your Oracle client can find the file.

For more detailed guidance on troubleshooting Oracle connectivity issues in Power BI, including managing on-premises data gateways, see the following documentation:

Manage your data source - Oracle - Power BI | Microsoft Learn

How to Get Your Question Answered Quickly 

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Noobie]

Hi Yongkang,

Thanks so much for your help! 

My TNS file is 100% error free as my teammates been using it eversince and my environment variable is correct too. I think it must have been because of application itself which I cannot find anything about how to resolve this.