ROracle bind range of dates

I want to send to oracle via ROracle query with bind parameters which inculde range of dates for a date column.

I try to run :

idsample <- 123
strdate <- "TO_DATE('01/02/2017','DD/MM/YYYY')"
enddate <- "TO_DATE('01/05/2017','DD/MM/YYYY')"

res <- dbGetQuery(myconn,"SELECT * FROM MYTABLE WHERE MYID = :1 AND MYDATE BETWEEN :2 AND :3", data=data.frame(MYID =idsample , MYDATE=c(strdate,enddate )))

but I get error :

"bind data does not match bind specification"

Solution

I could find no documentation which covers using more than one positional parameter, but if one parameter corresponds to a single column of a data frame, then by this logic three parameters should correspond to three columns:

idsample <- 123
strdate <- "TO_DATE('01/02/2017', 'DD/MM/YYYY')"
enddate <- "TO_DATE('01/05/2017', 'DD/MM/YYYY')"
res <- dbGetQuery(myconn,
                  paste0("SELECT * FROM MYTABLE WHERE MYID = :1 AND ",
                         "MYDATE BETWEEN TO_DATE(:2, 'DD/MM/YYYY') AND TO_DATE(:3, 'DD/MM/YYYY')"),
                   data=data.frame(idsample, strdate, enddate))

Note that there is nothing special about strdate and enddate from the point of view of the API, such that they should be passed as vector.

Edit:

The problem with making TO_DATE a parameter is that it will probably end up being escaped as a string. In other words, with my first approach you would end up with the following in your WHERE clause:

WHERE MYDATE BETWEEN
    'TO_DATE('01/02/2017','DD/MM/YYYY')' AND 'TO_DATE('01/05/2017','DD/MM/YYYY')'

In other words, the TO_DATE function calls ends up being a string. Instead, bind the date strings only.