08_make_oracle_cxn

Maritimes/Mar.utils - Establishing a COnnection to Oracle

May 2, 2018 - Mike McMahon (mike.mcmahon@dfo-mpo.gc.ca)

make_oracle_cxn.r

This function facilitates creating a connection to Oracle, and allows connection via RODBC or ROracle, depending on the value of usepkg. Credentials can be passed directly to the function, but if they're left blank, the function will prompt you.

Note that the connection object itself isn't what's returned, but a list of 3 objects:

usepkg chosen	test$usepkg	test$channel	test$thecmd
test=make_oracle_cxn(usepkg = 'rodbc')	'rodbc	"RODBC"	sqlQuery
test=make_oracle_cxn(usepkg = 'roracle')	'roracle'	"OraConnection"	dbGetQuery

RODBC

> test=make_oracle_cxn(usepkg = 'rodbc')
Oracle Username: username
[1] "username"
Oracle Password: mypassword
[1] "mypassword"
Oracle DSN (e.g. PTRAN): PTRAN
[1] "PTRAN"

Successfully connected to Oracle via RODBC

> class(test$channel)
[1] "RODBC"
#lets test it
> test$thecmd(test$channel,"SELECT * FROM dual")
  DUMMY
1     X

ROracle

#send the credentials
> test2=make_oracle_cxn(usepkg = 'roracle',fn.oracle.username = "username",fn.oracle.password = "password",fn.oracle.dsn = "PTRAN")

Successfully connected to Oracle via ROracle

> class(test2$channel)
[1] "OraConnection"
attr(,"package")
[1] "ROracle"
#lets test it
> test$thecmd(test$channel,"SELECT * FROM dual")
  DUMMY
1     X