Ads by ProfitSence
Close

Connecting R to an Oracle database with RJDBC

Last Updated on Wednesday 5th Oct 2022

rjdbc

  • If the source database is an Oracle database, you have a number of options.
  • ROracle , RODBC , RJDBC
  • The first step is to obtain the Oracle JDBC drivers.
  • Hostname or IP, e.g., database.company.com
  • Port,Service name or SID, e.g., ORCL
  • Username & Password

Set JAVA_HOME, set max. memory, and load rJava library

			
					Sys.setenv(JAVA_HOME=’C:/ProgramData/Oracle/Java/javapath’)
classPath=”c:/Program Files/DbVisualizer/jdbc/oracle/ojdbc6.jar”

			
	

Load RJDBC library

			
					library(RJDBC)

			
	

Create connection driver and open connection

rjdbc example

			
					jdbcDriver <- JDBC(driverClass="oracle.jdbc.OracleDriver", classPath="lib/ojdbc6.jar")
jdbcConnection <- dbConnect(jdbcDriver, "jdbc:oracle:thin:@//database.hostname.com:port/service_name_or_sid", "username", "password")

			
	

Query on the Oracle instance name

			
					instanceName <- dbGetQuery(jdbcConnection, "SELECT instance_name FROM v$instance")
print(instanceName)

			
	

Close connection

			
					dbDisconnect(jdbcConnection)