Search code examples
oracle-databasedatabase-connectionexcel-2013

Trying to connect Excel 2013 with Oracle - ORA-12154

I am trying to connect into Oracle through Power Query > From Database > From Oracle Database and then I type BI_TST, which is within my TNSNAMES (located in: C:\oracle\product\10.2.0\client_2\network\ADMIN), and has the connection below inside:

BI_TST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = srv-oracle3.mycompany.com.br)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = bitst)
    )
  )

Also i have the variable path configured as TNS_ADMIN=C:\oracle\product\10.2.0\client_2\network\ADMIN.

When I use SQL Developer to connect, not using TNS put setting all the information manually, it works!

When I try to connect using TNS within Excel I get the error:

Message=ORA-12154: TNS:não foi possível resolver o identificador de conexão especificado

    ErrorCode=-2146232008

Wich means in English: "could not resolve the connect identifier"

Could anyone please help me in this issue?

Thanks in advance!

Solution

  • Honestly, I've had my fill of tnsnames. I understand the idea behind it and can appreciate that, but I spend more time figuring out why it's not working than I do saving the extra keystrokes.

    In my opinon, just bypass TNSnames use Oracle's ezconnect:

    srv-oracle3.mycompany.com.br:1521/bitst

    In place of:

    BI_TST

    This should work for ODBC as well. If you can't direct connect through PowerQuery's Oracle driver, try going through ODBC. While it's an additional layer you probably don't want, I doubt you'll notice a performance difference, and it may abstract the difficulties you normally have to deal with.