1

I'm using ROracle on a Win7 machine running the following R version:

platform x86_64-w64-mingw32
arch x86_64
os mingw32
system x86_64, mingw32
status
major 3
minor 1.1
year 2014
month 07
day 10
svn rev 66115
language R
version.string R version 3.1.1 (2014-07-10) nickname Sock it to Me

Eventually, I'm going to move the script to a *nix machine, cron it, and run it with RScript.

I want to do something similar to:
select * from tablename where 'thingy' in ('string1','string2')

This would return two rows with all columns in SQLDeveloper (or Toad, etc).

(Ultimately, I want to pull results from one DB into a single column in a data.frame then use those results to loop through and pull results from a second db, but I also need to be able to do just this function as well.)

I'm following the documentation for RORacle from here.
I've also looked at this (which didn't get an answer):
Bound parameters in ROracle SELECT statements

When I attempt the query from ROracle, I get two different errors, depending on whether I try a dbGetQuery() or dbSendQuery().

As background, here are the versions, queries and data I'm using:

Driver name: Oracle (OCI)
Driver version: 1.1-11
Client version: 11.2.0.3.0

The connection information is standard:

library(ROracle)
ora <- dbDriver("Oracle")
dbcon <- dbConnect(ora, username = "username", password = "password", dbname = "dbnamefromTNS")

These two queries return the expected results:
rs_send <- dbSendQuery(dbcon, "select * from tablename where columname_A = 'thingy' and rownum <= 1000")
rs_get <- dbGetQuery(dbcon, "select * from tablename where columname_A = 'thingy' and rownum <= 1000")

That is to say, 1000 rows from tablename where 'thingy' exists in columnname_A.

I have a data.frame of one column, with two rows.

my.data = data.frame(RANDOM_STRING = as.character(c('string1', 'string2')))

and str(my.data) returns this:

str(my.data)
'data.frame': 2 obs. of 1 variable:
$ RANDOM_STRING: chr "string1" "string2"

my attempted queries are:

nope <- dbSendQuery(dbcon, "select * from tablename where column_A = 'thingy' and widget_name =:1", data = data.frame(widget_name =my.data$RANDOM_STRING))

which gives me an error of:
Error in .oci.SendQuery(conn, statement, data = data, prefetch = prefetch, : bind data does not match bind specification

and

not_this_either <- dbGetQuery(dbcon, "select * from tablename where column_A = 'thingy' and widget_name =:1", data = data.frame(widget_name =my.data$RANDOM_STRING))

which gives me an error of:
Error in .oci.GetQuery(conn, statement, data = data, prefetch = prefetch, : bind data has too many rows

I'm guessing that my problem is in the data=(widget_name=my.data$RANDOM_STRING) part of the queries, but haven't been able to rubber duck my way through it.

Also, I'm very curious as to why I get two separate and different errors depending on whether the queries use the send (and fetch later) format or the get format.

Community
  • 1
  • 1
ClintWeathers
  • 576
  • 7
  • 22

2 Answers2

1

If you like the tidyverse there's a slightly more compact way to achieve the above using purrr

library(ROracle)
library(purrr)
ora <- dbDriver("Oracle")
con <- dbConnect(ora, username = "username", password = "password", dbname = "yourdbnamefromTNSlist")

yourdatalist <- c(12345, 23456, 34567)

output <- map_df(yourdatalist, ~ dbGetQuery(con, "select * from YourTableNameHere where YOURCOLUMNNAME = :d", .x))
MarkGoble
  • 11
  • 1
0

Figured it out.
It wasn't a problem with Oracle or ROracle (I'd suspected this) but with my R code.
I stumbled over the answer trying to solve another problem.
This answer about "dynamic strings" was the thing that got me moving towards a solution.
It doesn't fit exactly, but close enough to rubberduck my way to an answer from there.

The trick is to wrap the whole thing in a function and run an ldply on it:

library(ROracle)
ora <- dbDriver("Oracle")
con <- dbConnect(ora, username = "username", password = "password", dbname = "yourdbnamefromTNSlist")

yourdatalist <- c(12345, 23456, 34567)

thisfinallyworks <- function(x) {
    dbGetQuery(con, "select * from YourTableNameHere where YOURCOLUMNNAME = :d", data = x)
}

ldply(yourdatalist, thisfinallyworks)

row1 of results where datapoint in YOURCOLUMNNAME = 12345
row2 of results where datapoint in YOURCOLUMNNAME = 23456
row3 of results where datapoint in YOURCOLUMNNAME = 34567
etc

Community
  • 1
  • 1
ClintWeathers
  • 576
  • 7
  • 22