没有tnsnames.ora的RODBC与Oracle的连接 [英] RODBC connectivity to Oracle without tnsnames.ora

问题描述

我正在尝试使用RODBC从R连接到Oracle，而不使用tnsnanes.ora.

I am trying to connect to Oracle from R using RODBC without using tnsnanes.ora.

我已经尝试过跟随字符串，但是没有一个起作用.

I have tried following strings, but none of them are working.

> con.text <- paste0("Driver={OracleODBC-11g};Dbq=//oracle.server:1527/database.pdw.prod;Uid=user;Pwd=pswd;")

> con.text <- paste0("Driver={OracleODBC-11g}; ",
         "CONNECTSTRING=(DESCRIPTION=(ADDRESS= (PROTOCOL = TCP)(HOST = oracle.server)(PORT = 1527))(CONNECT_DATA=(SERVICE_NAME = database.pdw.prod))); uid=user;pwd=pswd;")

> con.text <- paste0("Driver=", "OracleODBC-11g"
                     , ";Server=", "oracle.server"
                     , ";Database=", "database.pdw.prod"
                     , ";Uid=", "user"
                     , ";Pwd=", "pwd", ";")

> con.text <- paste0("Driver=", "OracleODBC-11g"
                      , ";Server=", "oracle.server"
                      , ";CONNECTSTRING=" , "(DESCRIPTION=(ADDRESS= (PROTOCOL = TCP)(HOST = oracle.server)(PORT = 1527))(CONNECT_DATA=(SERVICE_NAME = database.pdw.prod)))" 
                      , ";Database=", "database.pdw.prod"
                      , ";Uid=", "user"
                      , ";Pwd=", "pswd", ";")
> con1 <- odbcDriverConnect(connection = con.text)

但是对于所有这些字符串，我都会遇到以下错误:

But for all these strings I am getting following error:

Warning messages:
1: In odbcDriverConnect(connection = con.text) :
 [RODBC] ERROR: state HY000, code 12162, message [unixODBC][Oracle][ODBC][Ora]ORA-12162: TNS:net service name is incorrectly specified
2: In odbcDriverConnect(connection = con.text) : ODBC connection failed

OR

      1: In odbcDriverConnect(connection = con.text) :
  [RODBC] ERROR: state IM002, code 0, message [unixODBC][Driver Manager]Data source name not found, and no default driver specified

推荐答案

您要寻找的正确系统是

Conex     <- odbcDriverConnect("DRIVER=Oracle en OraClient11g_home2;UID=USERNAME;PWD=PASSWORD;DBQ=//HOSTNAME:PORT/ORACLE_SID;",
             believeNRows = FALSE)

Ex

Conex     <- odbcDriverConnect("DRIVER=Oracle en OraClient11g_home2;UID=John;PWD=Deere;DBQ=//fcoracleserver.youdomain:1521/TestEnvironment;",
             believeNRows = FALSE)

最困难的部分是找到驱动程序的名称，就像您看到的是西班牙语一样.

The hard part is to find the name of the Driver, as you can see mine is on spanish.

我要做的是首先使用C:\ Windows \ System32 \ odbcad32.exe创建一个ODBC连接，在这里您可以检查Oracle或SQL Server驱动程序的正确名称.

What I did is I create first a ODBC Conection using the C:\Windows\System32\odbcad32.exe, there you can check the right name of your Oracle or SQL Server driver.

创建连接后，就可以使用 在R上按odbcDataSources()键，以查看该连接并找出驱动程序.那真的是困难的部分.

Once you create the conection, you can use odbcDataSources() on R, to see that conection and to find out the driver. Thats really the hard part.

希望有帮助！

这篇关于没有tnsnames.ora的RODBC与Oracle的连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！