0

I'm trying to connect to an Oracle Database via ODBC. This connection will allow me to connect Power BI Desktop to Oracle. Using SQL Developer, I can managed to establish the connection, see the tables, views, etc.

Now, using Oracle Client 64-bit ODAC 12c Release 4 (12.1.0.2.4) for Windows x64, I get this tnsnames.ora file with the following configurations:

MYCONNECT=
   (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 123.xxxx.xxx..xxx)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCL) 
    )
  )

This all looks ok, but when trying to create the ODBC driver in windows, I get the following after entering the credentials:

enter image description here

And the error:

enter image description here

So, why can I establish the connection via SQL developer and not via ODBC driver?

And how can I solve this problem?

Thanks!

  • 1
    is this a matter that you connecting using wrong ODBC, 32bit vs 64bit. You probably just not pointing to right place somewhere – T.S. Mar 11 '19 at 16:29
  • Perhaps this help: https://stackoverflow.com/questions/28280883/determining-location-of-relevant-tnsnames-ora-file/28283924#28283924 – Wernfried Domscheit Mar 11 '19 at 16:38
  • As far as I know (but I am not sure), SQL developer does not use the `tnsnames.ora` file - and the ODBC driver does not find it. – Wernfried Domscheit Mar 11 '19 at 16:39
  • 1
    @WernfriedDomscheit Sql Developer has multiple modes of connection. – T.S. Mar 11 '19 at 17:28
  • @T.S. I'm using 64-bit because my system is 64bit. How can I check where to find what is connecting to what? –  Mar 11 '19 at 17:47
  • 1
    Where is your `tnsnames.ora` file located? Did you set `TNS_ADMIN` environment variable or did you set `TNS_ADMIN` in your Registry? – Wernfried Domscheit Mar 11 '19 at 18:37
  • *"I'm using 64-bit because my system is 64bit."* - but does sql developer use x64?. Plus, I generally not sure what is going on. If you use TNS then why do you need ODBC? It is one or another. You can put SID and all in Sql devaloper directly, without TNS – T.S. Mar 11 '19 at 18:39
  • @WernfriedDomscheit my tnsnames.ora file is in my D drive, I can't anwser your second question, I have no idea what TNS_ADMIN is. I'm just trying to connect Power BI to Oracle, like I have done with SQL Server DB, Access, MySQL DB, and a constellation of web API. But with ORACLE!? Too F***CKING complicated! Any help would be appreciated –  Mar 11 '19 at 19:37
  • @T.S.All I'm trying to do is to connect Power BI Desktop to an Oracle DB, but it seems like mission impossible. It's been 2 weeks now and I'm getting crazy. I'm just following the explanation as per this post: https://community.powerbi.com/t5/Desktop/Connect-To-Oracle-Database/td-p/188217 –  Mar 11 '19 at 19:40
  • What I see from your link, is that ODBC DSN was used only to test connection. But you can do it using `tnsping` (although, this is partial test - only tests that service name is connected). And then, what you put as TNS name (in your case `MYCONNECT`) will be going into field `Server`. If you installed Ora client, `tnsnamemes.ora` should be under folder `network/admin` – T.S. Mar 11 '19 at 20:32
  • Thanks, tsnames.ora is in \product\12.1.0\client_1\Network\Admin –  Mar 11 '19 at 21:25
  • so, somewhere in your `\product\12.1.0\client_1` you have `tnsping.exe`. Open CMD window, type or paste full path and tns name `c:\....\product\12.1.0\client_1\....\tnsping.exe MYCONNECT`. It should give you `OK`. If not `OK`, you have issue in your TNS or DB server – T.S. Mar 11 '19 at 21:52
  • @NunoNogueira, come on - you should know what an "environment variable" is and how to set it. If you don't know, use google. – Wernfried Domscheit Mar 12 '19 at 15:24
  • Please show us what you put in ODBC Driver Configuration, i.e. Data Source Name, TNS Service Name, etc. – Wernfried Domscheit Mar 12 '19 at 15:31

0 Answers0