尝试将 Excel 2013 与 Oracle 连接 - ORA-12154
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24809391/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Trying to connect Excel 2013 with Oracle - ORA-12154
提问by Lucas Rezende
I am trying to connect into Oracle through Power Query > From Database > From Oracle Databaseand 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:
我正在尝试通过连接到 Oracle Power Query > From Database > From Oracle Database，然后键入BI_TST，它在我的 TNSNAMES 中（位于 : C:\oracle\product\10.2.0\client_2\network\ADMIN），并且在下面有连接：
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.
我也将变量路径配置为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!
当我使用SQL Developer连接时，没有使用TNS手动设置所有信息，它有效！
When I try to connect using TNS within Excel I get the error:
当我尝试在 Excel 中使用 TNS 进行连接时，出现错误：
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!
提前致谢！
回答by Hambone
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.
老实说，我已经填满了 tnsnames。我理解它背后的想法并且可以理解这一点，但是与节省额外的击键次数相比，我花了更多的时间来弄清楚为什么它不起作用。
In my opinon, just bypass TNSnames use Oracle's ezconnect:
在我看来，只需绕过 TNSnames 使用 Oracle 的 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.
这也适用于 ODBC。如果您无法通过 PowerQuery 的 Oracle 驱动程序直接连接，请尝试通过 ODBC。虽然它是您可能不想要的附加层，但我怀疑您会注意到性能差异，并且它可能会抽象出您通常必须处理的困难。
回答by app_sciences
To clarify Hambone's response, to connect Excel to Oracle via Oracle client driver without using TNS Names:
为了澄清 Hambane 的回应，通过 Oracle 客户端驱动程序将 Excel 连接到 Oracle，而不使用 TNS 名称：
- Go to Control Panel > Administrative Tools > ODBC Data Sources
- Go to File DSN tab
- Choose Oracle driver (e.g. Oracle11gr1)
- Provide a name for the new data source (any name)
- When prompted for Service Name, enter host:port/servicename e.g. db123:1521/service123
- 转至控制面板 > 管理工具 > ODBC 数据源
- 转到文件 DSN 选项卡
- 选择 Oracle 驱动程序（例如 Oracle11gr1）
- 为新数据源提供一个名称（任何名称）
- 当提示输入服务名称时，输入主机：端口/服务名称，例如 db123:1521/service123
Hope this works for everyone.
希望这对每个人都有效。
