CODEWITHSUNDEEP
roracle-database
user 123342
user 123342

Reputation: 483

SQL statement with native R object

I am using an Oracle SQL database, which I am querying from R. Is it possible to query the Oracle DB using native R objects in the statement, for example to join a DB table to a dataframe?

Setting where conditions works nicely from R like so:

oraConnect::load_from_db(sprintf('select * from dual where DUMMY = %s', 'X'))

Is it possible, for example, to join a DB data table to an R data table like so (does not work as written) by inserting an R object into an oracle SQL statement?:

testdf = data.frame(DUMMY = 'X')
oraConnect::load_from_db(sprintf('select DUMMY from dual join %s rdf on dual.DUMMY = rdf.DUMMY', testdf))

Upvotes: 0

Views: 481

Answers (1)

MDEWITT
MDEWITT

Reputation: 2368

This answer does not use the oraConnect package, but can be used to connect to Oracle databases as long as you have an odbc connection set-up on your machine.

Connecting

First load the packages and connect to the database

library(odbc)
library(DBI)
con <- dbConnect(odbc(), 'YOUR_DSN')

After you have your connection set, you can do a quick test query. I use the SendQuery, Fetch, and Clear. You don't have to do this, but it avoids some of the conflicts etc and makes dealing with bigger data pulls easier. Note that "schema_name" is the schema name in your database if required.

query <- DBI::dbSendQuery(con, "SELECT * FROM schema_name.table WHERE ROWNUM <= 10")
out <- DBI::dbFetch(query)
DBI::dbClearResult(query)

The out object will have your data.

Autogenerating Queries

Now this is where I will use the glue package

library(glue)

dat  <- c("apples", "pears", "oranges")

(my_new_query <- glue("SELECT * FROM schema_name.table_name WHERE id = {dat}"))

# SELECT * FROM schema_name.table_name WHERE id = apples
# SELECT * FROM schema_name.table_name WHERE id = pears
# SELECT * FROM schema_name.table_name WHERE id = oranges

Or something like 

table_1 <- "cities"
table_2 <- "states"

(my_new_query <- glue("SELECT a.* FROM schema_name.{table_1} LEFT JOIN schema_name.{table_2} ON..."))

#SELECT a.* FROM schema_name.cities LEFT JOIN schema_name.states ON...

Which you could then submit to your database

query <- DBI::dbSendQuery(con, my_new_query)
out <- DBI::dbFetch(query)
DBI::dbClearResult(query)

You can also add things within the glue statements like making a list.


dat  <- c("apples", "pears", "oranges")

(my_new_query <- glue("SELECT * FROM schema_name.table_name WHERE id = IN({glue_collapse(dat, sep = ',')})"))

#SELECT * FROM schema_name.table_name WHERE id = IN(apples,pears,oranges)

thus everything can be extended.

Upvotes: 1

Related Questions