I would like to connect to an oracle database using the R package odbc.
The following gives an error:
host <- 'something'
port <- 1523
svc <- "this"
pass <- '***'
user <- 'me'
con <- odbc::dbConnect(DBI::dbDriver("Oracle"),
UID=user,
PWD=pass,
dbname = svc)
ORA-01017: invalid username/password; logon denied
The full version does the same:
connect.string <- paste(
"(DESCRIPTION=",
"(ADDRESS=(PROTOCOL=tcp)(HOST=", host, ")(PORT=", port, "))",
"(CONNECT_DATA=(SERVICE_NAME=", svc, ")))", sep = "")
con <- odbc::dbConnect(DBI::dbDriver("Oracle"),
UID=user,
PWD=pass,
dbname = connect.string)
ORA-01017: invalid username/password; logon denied
Here is the info from dbDriver:
DBI::dbDriver("Oracle")
Driver name: Oracle (OCI)
Driver version: 1.3-1
Client version: 12.2.0.1.0
Connections processed: 0
Open connections: 0
Interruptible: FALSE
Unicode data as utf8: TRUE
Oracle type attributes: FALSE
But when I use RODBC with the same information, it all works fine:
connect.string2 <- paste0("Driver={Oracle in OraClient12Home1};Dbq=",svc,";Uid=",user,";Pwd=",pass,";")
con <-RODBC::odbcDriverConnect(connect.string2)
RODBC::sqlTables(con, tableType = "TABLE") # looks perfect
I would really like to keep working with odbc because I have a package that is build on it (connection to several different databases, and odbc always worked fine so far). Why is it not working this time?
I don't know why exactly the individual-arguments method doesn't work, but you can always use your connection string with DBI
/odbc
as well. From https://github.com/r-dbi/odbc#connection-strings,
Alternatively you can pass a complete connection string as the .connection_string argument. The Connection Strings Reference is a useful resource that has example connection strings for a large variety of databases.
library(DBI) con <- dbConnect(odbc::odbc(), .connection_string = "Driver={PostgreSQL Driver};Uid=postgres;Pwd=password;Host=localhost;Port=5432;Database=test_db;")
From this, I suspect you can do:
connect.string2 <- paste0("Driver={Oracle in OraClient12Home1};Dbq=",svc,";Uid=",user,";Pwd=",pass,";")
con <- odbc::dbConnect(DBI::dbDriver("Oracle"),
.connection_string = connect.string2)