Search code examples
oracle-databaseoracle11godbcpyodbctnsnames

"ORA-12560: TNS:protocol adapter error" when bypassing tnsnames.ora using full connection string and EZCONNECT

Attempting to use either connection string to bypass tnsnames.ora (full connection string and EZCONNECT) returns the following error when trying to connect to the database:

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)')

I've tried using the following connection strings from 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;'

I can connect to the database using sqlplus and 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>

I doubt it's a driver issue, because using a different driver returns a different error:

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

Why does the connection string work for sqlplus but not for ODBC?

Solution

  • When you use the Oracle ODBC driver then the connection string should be like this: 

    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;

    See Oracle in OraClient11g_home1 connection strings

    For the deprecated Microsoft driver it would be

    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;

    See Microsoft ODBC for Oracle connection strings

    Please note, the Microsoft ODBC for Oracle is available only for 32-Bit