In the ROracle package, dbSendQuery is defined as "dbSendQuery" (conn, statement, data=Null...), with the data argument specified as “a data.frame specifying bind data”.
My question lies with how the data argument is read by the function.
For example, I am able to run the following simplified function:
statement = “SELECT * FROM DATA WHERE DATE BETWEEN :START AND :END”
bind = data.frame(START = DT1, END = DT2)
DT1 = ‘01-jan-2020’
DT2 = ‘31-jan-2020’
dbSendQuery(con, statement = state, data = bind)
However, I receive an error message when I try to run the function with a SQL statement that calls for the same bind parameters multiple times.
For example, if I try to run the above code, with the following simplified statement, the code doesn’t work:
statement =
WITH DATA1 AS (SELECT * FROM TABLE1 WHERE DATE BETWEEN :START AND :END),
DATA2 AS (SELECT * FROM TABLE2 WHERE DATE BETWEEN :START AND :END),
DATA3 AS (SELECT * FROM DATA2 LEFT JOIN DATA1 ON DATA2.COLUMN = DATA1.COLUMN)
SELECT *
FROM DATA3
UNION ALL
SELECT *
FROM DATA3 A
JOIN
(SELECT * FROM TABLE2
WHERE DATE BETWEEN :START AND :END) B
ON A.DATE = B.DATE"
However, the function does work if I run dbSendQuery with the following statement and bind:
statement =
"WITH DATA1 AS (SELECT * FROM TABLE1 WHERE DATE BETWEEN :START1 AND :END1),
DATA2 AS (SELECT * FROM TABLE2 WHERE DATE BETWEEN :START2 AND :END2),
DATA3 AS (SELECT * FROM DATA2 LEFT JOIN DATA1 ON DATA2.COLUMN = DATA1.COLUMN)
SELECT *
FROM DATA3
UNION ALL
SELECT *
FROM DATA3 A
JOIN
(SELECT * FROM TABLE2
WHERE DATE BETWEEN :START3 AND :END3) B
ON A.DATE = B.DATE"
bind = data.frame(START1, = DT1, START2 = DT1, START3 = DT1, END1 = DT2, END2 = DT2, END3 = DT2).
While the above method works, I’m curious as to why I am unable to run dbSendQuery with the simple dataframe
bind = data.frame(START = DT1, END = DT2)
when the statement repeatedly uses the same bind parameters.
I have also tried running dbSendQuery with
bind = data.frame(START = rep(DT1,3), END = rep(DT2,3))
to no success.
After some discussion with the ROracle owners, it seems that you need one entry in the bind data frame for each use of the bind variable in the SQL statement. This is true even with named binds:
bvn = as.numeric(1)
attr(bvn, "ora.parameter_name") <- "bv";
df <- data.frame(bvn, bvn)
dbGetQuery(con, "select * from dual where 1 = :bv and 1 = :bv", df)