R Oracle DB connection fails with dbPool but succeeds with dbConnect

[mmccarthy404]

I'm attempting to refactor older code to make use of DB pools using the pool package's dbPool function.

Historically I've been using the DBI package's dbConnect function without issue. I can successfully create a connection to my Oracle database with the below code (all credentials are faked):

conn <- DBI::dbConnect(
  ROracle::Oracle(),
  "database.abcd1234.us-east-1.rds.amazonaws.com/orcl",
  username="username",
  password="hunter2"
)

However, when I use the same credentials in the same development environment to attempt to create a pool like this:

pool <- pool::dbPool(
  drv      = ROracle::Oracle(),
  dbname   = "orcl",
  host     = "database.abcd1234.us-east-1.rds.amazonaws.com",
  username = "username",
  password = "hunter2"
)

I get an error:

Error in .oci.Connect(.oci.drv(), username = username, password = password,  : 
  ORA-12162: TNS:net service name is incorrectly specified

I've used dbPool before but with Postgres databases instead of Oracle, and for Postgres, it just worked! I'm thinking that because my credentials work fine for dbConnect, there must be some small thing I'm missing that's needed for dbPool to work correctly too

[edgararuiz]

Hi, can you try matching the arguments on both command calls? I realize that pool requires all arguments to be named. So maybe let's try using host ="database.abcd1234.us-east-1.rds.amazonaws.com/orcl" on both dbConnect() and dbPool() calls. I believe all pool does is pass the arguments to a dbConnect() call it makes, so I get the sense that this is due to both calls being different.

[mmccarthy404]

I didn't realize that the arguments for dbPool would differ depending on the DB drivers!

@edgararuiz you were correct, I need to create the pool like this:

pool <- pool::dbPool(
  drv      = ROracle::Oracle(),
  dbname   = "database.abcd1234.us-east-1.rds.amazonaws.com/orcl",
  username = "username",
  password = "hunter2"
)