oracle - "ORA-12560: TNS:protocol adapter error"使用完整连接字符串和 EZCONNECT 绕过 tnsnames.ora 时

Question

尝试使用任一连接字符串绕过 tnsnames.ora(完整连接字符串和 EZCONNECT)会在尝试连接到数据库时返回以下错误:

pyodbc.Error: ('HY00', '[HY00] [Oracle][ODBC][Ora]ORA-12560: TNS:protocol adapter error\n (12560) (SQLDriverConnect);[Oracle][ODBC][Ora]ORA-12560: TNS:protocol adapter error\n (12560)')

我尝试使用来自 https://www.connectionstrings.com/oracle/ 的以下连接字符串:

Driver={Oracle in OraClient11g_home1}; Data Source=uid/pwd@//host:port/service_name;

Driver={Oracle in OraClient11g_home1};
Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=host)(PORT=port))) 
                         (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=service_name)));
User Id=uid;Password=pwd;'

我可以使用 sqlplus 和 EZCONNECT 连接到数据库:

PS: C:\>sqlplus uid/pwd@//host:port/service_name

SQL*PLUS: Release 11.2.0.1.0 Production on Thu Aug 30 11:56:22 2018

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Connected to:
Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production

SQL>

我怀疑这是驱动程序问题，因为使用不同的驱动程序会返回不同的错误:

pyodbc.InterfaceError: ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')

为什么连接字符串适用于 sqlplus 而不适用于 ODBC？

Answer 1

当您使用 Oracle ODBC 驱动程序时，连接字符串应如下所示:

Driver={Oracle in OraClient11g_home1};
DBQ={(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=host)(PORT=port))) 
                         (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=service_name)))};
Uid=uid;Pwd=pwd;

见 Oracle in OraClient11g_home1 connection strings

对于 deprecated Microsoft 驱动程序将是

Driver={Microsoft ODBC for Oracle};
Server={(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=host)(PORT=port))) 
                         (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=service_name)))};
Uid=uid;Pwd=pwd;

见 Microsoft ODBC for Oracle connection strings

请注意，Microsoft ODBC for Oracle 仅适用于 32 位