Using ORE 1.4 on Oracle 12.1c with pluggable databases

It is possible to use Oracle database tables in the R statistical software. And this is a very useful approach (if you know R's capabilities). The fact that you are on this blog now may means that you had no success trying to use tables in R and that you received ORA-12541 once more. To solve this problem, you can omit the next paragraph. However, for getting started with Oracle 12c and R you should take a look at the documentation provided by Oracle. Clear and brief it says that you have to do the following things to use Oracle database in R:

• Install the Oracle Instant Client if you don't have already from http://www.oracle.com/technetwork/database/features/instant-client/
• Install Oracle's R distribution (ORD) from https://oss.oracle.com/OR
• Modify the PATH variable for the  path of Instant Client
• Set the environment variable OCI_LIB64 with the path to the Instant Client
• Install ORE Client Package for R from http://www.oracle.com/technetwork/database/options/advanced-analytics/r -enterprise/ore-downloads-1502823.html
• Install ORE Supporting Package for R from http://www.oracle.com/technetwork/database/options/advanced-analytics/r -enterprise/ore-downloads-1502823.html

First steps in R

Finally you should be able to connect to the Oracle database by loading the ORE package in R

library(ORE)

and then using the following command to connect:

ore.connect(user="RQUSER", sid="orcl", host="SVR3", password="RQUSERpsw", port=1521, all=TRUE) 

First problems in Oracle 12.1c

But this won't work on Oracle 12.1c with plugable databases (aka. Multi-Tenant). The problem on Oracle 12.1c is, that the host will always be the container database and not the plugged database containing the data you want to use. You need the TNS service name to connect. And despite you might have a well-configurated TNSNAMES.ORA file and all paths set, a connection with the statement
ore.connect(user="RQUSER", service_name="service", password="RQUSERpsw", all=TRUE) will not work and you will always receive an error message: Error in .oci.Connect(.oci.drv(), username = username, password = password, : ORA-12541: TNS: No Listener 

The solution for oracle 12.1c

The actual problem is, that you need to specify also the host name, though you have a TNSNAMES.ORA file. It seems like R isn't using this file and instead it uses the host and the service name directly. So You have to modify the statement by assigning the host name from the TNSNAMES.ORA file:

ore.connect(user="user name", service_name="service", host="host" password="password", all=TRUE)

Where you can find both, the service_name and the host in the corresponding attributes in the file.