当前位置：theitroad>database Oracle - 使用 MS Access 的 ODBC 连接错误 (ORA-12154)

database Oracle - 使用 MS Access 的 ODBC 连接错误 (ORA-12154)

声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow 原文地址: http://stackoverflow.com/questions/247317/
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

提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-09-08 07:03:37 　来源:igfitidea点击:

Oracle - ODBC connection using MS Access error (ORA-12154)

databaseoraclems-accessodbcora-12154

提问by Joe

I am trying to use MS access to connect to a Oracle database. I keep on getting the following error message:

我正在尝试使用 MS 访问连接到 Oracle 数据库。我不断收到以下错误消息：

ORA-12154: TSN- could not resolve the connect identifier secified

ORA-12154: TSN- 无法解析指定的连接标识符

The Oracle Drivers OracleClient10g can verify that the database server exists.

Oracle Drivers OracleClient10g 可以验证数据库服务器是否存在。

I have a section in my tsnnames.ora file that looks like this: UBASEP10G = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = bxxx-xxx.yyyy.com)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = UBASE) ) )

我的 tsnnames.ora 文件中有一个部分如下所示： UBASEP10G = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = bxxx-xxx.yyyy.com)(PORT = 1521)) ) ( CONNECT_DATA = (SERVICE_NAME = UBASE) ) )

per my attempts to get this error resolves I added this to the sqlnet.ora file:

根据我尝试解决此错误的尝试，我将其添加到 sqlnet.ora 文件中：

NAMES.DIRECTORY_PATH= (HOSTNAME, ONAMES, TNSNAMES,LDAP,EZCONNECT)

NAMES.DIRECTORY_PATH=（主机名、ONAMES、TNSNAMES、LDAP、EZCONNECT）

When using the Windows ODBC driver configuration utility it asks for the following following information DATA SOURCE NAME : MYSOURCE NAME TSN SERVICE NAME:UBASEP10G USERID:MYUSERID

使用 Windows ODBC 驱动程序配置实用程序时，它要求提供以下信息 数据源名称：MYSOURCE NAME TSN 服务名称：UBASEP10G USERID:MYUSERID

any suggestions ?????

有什么建议 ？？？？？

回答by Joseph Bui

I don't have Access, but using Excel 2007, I had to do the following:

我没有 Access，但使用 Excel 2007，我必须执行以下操作：

  1. Open ODBC Administrator (in the Administrator Control Panel)
  2. For either User DSN or System DSN, click Add...
  3. Select "Oracle in OraDb10g_home1" as the driver
  4. In the Oracle ODBC Driver Configuration, I entered:
    • Data Source Name: myOracleDsn
    • Description: This is my DSN for my Oracle Database
    • TNS Service Name: oratns
    • User ID: scott
  5. Click Test Connection, and enter "tiger" for the password. Obviously, I'm connecting to my scott/tiger sample Oracle database. Also, when I installed Oracle, I picked oratns as my TNS name. The other two values above are arbitrary -- you'll see them in dialogs in Excel.
  6. If the test works, click OK.
  7. Go to Excel and make a new connection to a DSN. The dialog will show "myOracleDsn" as an option. Select it, enter "tiger" for the password again, and you should get a working connection.
  1. 打开 ODBC 管理员（在管理员控制面板中）
  2. 对于用户 DSN 或系统 DSN，单击添加...
  3. 选择“Oracle in OraDb10g_home1”作为驱动
  4. 在 Oracle ODBC 驱动程序配置中，我输入：
    • 数据源名称：myOracleDsn
    • 描述：这是我的 Oracle 数据库的 DSN
    • TNS 服务名称：oratns
    • 用户名：scott
  5. 单击“测试连接”，然后输入“tiger”作为密码。显然，我正在连接到我的 scott/tiger 示例 Oracle 数据库。另外，当我安装 Oracle 时，我选择了 oratns 作为我的 TNS 名称。上面的另外两个值是任意的——您将在 Excel 的对话框中看到它们。
  6. 如果测试有效，请单击“确定”。
  7. 转到 Excel 并建立与 DSN 的新连接。该对话框将显示“myOracleDsn”作为一个选项。选择它，再次输入“tiger”作为密码，您应该会获得一个有效的连接。

回答by DCookie

Try changing (CONNECT_DATA = (SERVICE_NAME = UBASE) ) to (CONNECT_DATA = (SID = UBASE) ) in your TNSNAMES.ora file.

尝试在您的 TNSNAMES.ora 文件中将 (CONNECT_DATA = (SERVICE_NAME = UBASE) ) 更改为 (CONNECT_DATA = (SID = UBASE) )。

ServiceName and SID aren't necessarily the same and consequently aren't always interchangeable.

ServiceName 和 SID 不一定相同，因此并不总是可以互换的。

The SERVICENAME parameter refers to a particular GLOBAL_DBNAME specified in the listener.ora file on the database server. It's an alias for an instance on the server. You can have multiple servicenames on a server referring to the same SID. The SID parameter refers to a particular instance on that server.

SERVICENAME 参数指的是在数据库服务器上的 listener.ora 文件中指定的特定 GLOBAL_DBNAME。它是服务器上实例的别名。一个服务器上可以有多个服务名称引用同一个 SID。SID 参数指的是该服务器上的特定实例。

The advantage of using servicename on the client side is that the DBA can change the actual instance being referenced by a servicename transparently to the clients using that name. I can have this on the server listener.ora file:

在客户端使用 servicename 的优点是 DBA 可以更改 servicename 引用的实际实例，对使用该名称的客户端透明。我可以在服务器 listener.ora 文件中使用它：

(SID_DESC =
  (GLOBAL_DBNAME = THESERVICE)
  (ORACLE_HOME = d:\oracle.2.0_DB)
  (SID_NAME = SID1)

Later, I can change the actual database being referenced by switching the listener.ora configuration:

稍后，我可以通过切换 listener.ora 配置来更改所引用的实际数据库：

(SID_DESC =
  (GLOBAL_DBNAME = THESERVICE)
  (ORACLE_HOME = d:\oracle.2.0_DB)
  (SID_NAME = SID2)

and nobody's the wiser on the client side. No changes were necessary in the tnsnames.ora files on the clients.

在客户端，没有人是更明智的。客户端上的 tnsnames.ora 文件无需更改。

回答by BQ.

Can you log in to the database in question via SQL*Plus? Doing this from another machine with a working connection (or the DB server itself) is fine also.

您可以通过 SQL*Plus 登录到相关数据库吗？从具有工作连接的另一台机器（或数据库服务器本身）执行此操作也很好。

If so, run this:

如果是这样，请运行：

select value from v$parameter where name='service_names';

In your TNSNAMES.ORA, use one of the values listed there for the SERVICE_NAME.

在您的 TNSNAMES.ORA 中，使用其中为 SERVICE_NAME 列出的值之一。

In you ODBC connection, all you'll need is to set the TNS Service Name to the name you used above, "UBASEP10G"

在您的 ODBC 连接中，您只需要将 TNS 服务名称设置为您在上面使用的名称“UBASEP10G”

回答by DCookie

Let's back up to square one. Open a command window and connect to your database:

让我们回到第一个。打开命令窗口并连接到您的数据库：

sqlplus myuserid/mypassword@UBASEP10G

sqlplus myuserid/mypassword@UBASEP10G

Does this connect successfully?

这是否连接成功？

Since the answer is no, is there a way you CAN connect successfully to this database? BQ is correct, your problem is with the servicename of UBASE. You need to determine what the listener on the server thinks the name of that database is. Do you have access to the server? Can you execute the command "lsnrctl status" on the server? This will tell you the services that are registered with the listener, and look something like this:

既然答案是否定的，那么有没有办法可以成功连接到这个数据库呢？BQ是正确的，你的问题是UBASE的服务名。您需要确定服务器上的侦听器认为该数据库的名称是什么。您可以访问服务器吗？你能在服务器上执行命令“lsnrctl status”吗？这将告诉您注册到侦听器的服务，如下所示：

Services Summary...
Service "UBASE" has 1 instance(s).
  Instance "UBASE", status READY, has 1 handler(s) for this service...

回答by BQ.

Try tnsping and report your results.

尝试 tnsping 并报告您的结果。

Bad:

坏的：

C:\>tnsping notreal.world

TNS Ping Utility for 32-bit Windows: Version 9.2.0.5.0 - Production on 29-OCT-2008 15:56:47

Copyright (c) 1997 Oracle Corporation.  All rights reserved.

Used parameter files:
C:\oracle\ora92\network\admin\sqlnet.ora

TNS-03505: Failed to resolve name

Good:

好的：

O:\>tnsping real.world

TNS Ping Utility for 32-bit Windows: Version 9.2.0.5.0 - Production on 29-OCT-2008 15:57:42

Copyright (c) 1997 Oracle Corporation.  All rights reserved.

Used parameter files:

C:\oracle\ora92\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = DBSERVER.DOMAIN.COM)(PORT = 1521)) (LOAD_BALANCE = YES) (FAILOVER = YES))
(CONNECT_DATA = (SERVICE_NAME = REAL.WORLD) 
(FAILOVER_MODE = (TYPE = SELECT) (METHOD = BASIC) (RETRIES = 10) (DELAY = 3))))
OK (40 msec)

相关推荐

database 在 VB6 中使用 SQLITE

database 在 VB6 中使用 SQLITE

database 您是否对数据库项目使用源代码管理？

database 您是否对数据库项目使用源代码管理？

database 在 informix 中查询的行号

database 在 informix 中查询的行号

database 将 Excel 数据表加载到 Oracle 数据库

database 将 Excel 数据表加载到 Oracle 数据库

相关推荐
最近更新
标签