Power BI Connecting with Oracle Database

[santhoshpowerbi]

Hi Experts,

I am a newbie to this community.

I am trying to install Oracle ODAC (64-bit ODAC 12c Release 4 (12.1.0.2.4) for Windows x64 - 287 MB) from this link (https://www.oracle.com/database/technologies/odac-downloads.html) in my desktop having windows server 2012 R2 Operating System.

While installing the ODAC, the prerequisites ( Architecture / PATH ) were "Failed", however, I choose the "Ignore All" option, and then the installation was successful.

In that same Machine, I have my Power BI Desktop Version: 2.88.1144.0 64-bit (December 2020). When I am trying to connect to Oracle Database it's throwing me the below error.

https://docs.microsoft.com/en-us/power-bi/connect-data/service-gateway-onprem-manage-oracle  

I read the above link also, but could not find the solution.

Please guide me on how to fix this issue.

Thank You

Santhosh

Windows: Server 2012 R2

ODAC Version : 12.1.0.2.4
Oracle Database version: Oracle 19c

[amitchandak]

@santhoshpowerbi , add oracle install directory path to the Window Environment variable and check

example

[santhoshpowerbi]

Hi Amit, 

Thanks for your reply. I have added the path but it's still the same.

Path = E:\app\client\famis\product\12.1.0\client_1\odp.net\bin;E:\app\client\famis\product\12.1.0\client_1;E:\app\client\famis\product\12.1.0\client_1\bin;%SystemRoot%\system32;%SystemRoot%;%SystemRoot%\System32\Wbem;%SYSTEMROOT%\System32\WindowsPowerShell\v1.0\;C:\Program Files\Microsoft VS Code\bin

Thanks

Santhosh

[v-yingjl]

Hi @santhoshpowerbi ,

Please follow this viedo about installing oracle 19c on Windows Server 2012 R2:

Oracle 19c Installation on Windows Server 2012 R2 Step by Step | Oracle 19c on Windows Server 2012! 

If your oracle could work fine, try to update your power bi desktop to the latest version(May 2021)

In addition, please note:

Choose a version of Oracle Data Access Client (ODAC) which is compatible with your Oracle Server. For instance, ODAC 12.x does not always support Oracle Server version 9. Choose the Windows installer of the Oracle Client. During the setup of the Oracle client, make sure you enable Configure ODP.NET and/or Oracle Providers for ASP.NET at machine-wide level by selecting the corresponding checkbox during the setup wizard. Some versions of the Oracle client wizard selects the checkbox by default, others do not. Make sure that checkbox is selected so that Power BI can connect to your Oracle database.

Refer: Connect to an Oracle database with Power BI Desktop 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[santhoshpowerbi]

Hi Yingjie Li,

Thank you for your reply.

As suggested, I have updated my POWER BI Desktop to the latest version and

I have followed the two links "Connect to an Oracle database with Power BI Desktop " and downloaded ODAC from it and installed and configured the tnsnames.ora file.

For a Test Check, I run the sqlplus to connect the destination database from my local machine (Where ODAC was installed), and it's successful.

Whereas POWERBI Connection is still giving the error.

Please suggest, what I am missing here.

Thanks

Santhosh

[v-yingjl · Accepted solution]

Hi @santhoshpowerbi ,

Please note:

You might encounter any of several errors from Oracle when the naming syntax is either incorrect or not configured properly:

• ORA-12154: TNS:could not resolve the connect identifier specified.
• ORA-12514: TNS:listener does not currently know of service requested in connect descriptor.
• ORA-12541: TNS:no listener.
• ORA-12170: TNS:connect timeout occurred.
• ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA.

These errors might occur if the Oracle client either isn't installed or isn't configured properly. If it's installed, verify that the tnsnames.ora file is properly configured and you're using the proper net_service_name. You also need to make sure that the net_service_name is the same between the machine that uses Power BI Desktop and the machine that runs the gateway. 

For configuring TNS listener and .ora file, you can refer:

1. Oracle Network Configuration (listener.ora , tnsnames.ora , sqlnet.ora) 
2. Create TNS in Oracle 19c Windows 10 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.