使用RJDBC在R中执行参数化SQL循环查询时遇报错求助

我常通过R执行SQL查询，此前已提交过相关问题，现需解决使用RJDBC批量执行SQL查询时遇到的报错。已导入数据框，想要基于数据框中的contact_id批量查询权限数据，但执行代码时触发报错，推测与RJDBC驱动的参数处理逻辑有关。

library(RJDBC)  

# 创建用户专属文件路径
classpath_name="A:/ojdbc8.jar"

# 创建连接驱动并打开连接
jdbcDriver <- JDBC(driverClass="oracle.jdbc.driver.OracleDriver", classPath=classpath_name)  ## 公司提供的jar8文件用于连接数据库（另有2个jar文件可选）
jdbcConnection <- dbConnect(jdbcDriver, "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=lhs-dbprod.forteresearchapps.com)(PORT=2879))(CONNECT_DATA=(SERVICE_NAME=pdblhs)))", "database", "adfhkjsdabfiewubbksdjblablablabla")  ## 数据库主机信息等


Contacts<- dbGetQuery(jdbcConnection, 
                      "select contact_id, username from lhs_oncore_prod.rv_contact where not username='barcode_user'" ) 


(qmarks <- paste(rep("?", nrow(Contacts)), collapse = ","))


Access <- dbGetQuery(jdbcConnection,
                 paste("SELECT priv_check.contact_id, priv_check.protocol_id
     FROM LHS_ONCORE_PROD.sv_user_pcl_permission priv_check
     WHERE priv_check.function_name = 'CRPT-Subject Visits'
     AND priv_check.contact_id in (", qmarks, ")"),
                 params = as.list(Contacts$CONTACT_ID) )

Access <- do.call(dbGetQuery, c(list(jdbcConnection, "SELECT priv_check.contact_id, priv_check.protocol_id FROM LHS_ONCORE_PROD.sv_user_pcl_permission priv_check WHERE priv_check.function_name = 'CRPT-Subject Visits' AND priv_check.contact_id in (", qmarks, ")"), params = as.list(Contacts$CONTACT_ID) )) 

• 直接拼接ID字符串（低风险场景）：如果contact_id是整数类型，可直接将ID拼接成SQL字符串，避开RJDBC的参数数量限制（注意：若ID包含用户输入需警惕SQL注入）：

contact_ids <- paste(Contacts$CONTACT_ID, collapse = ",")
Access <- dbGetQuery(jdbcConnection,
                     paste("SELECT priv_check.contact_id, priv_check.protocol_id
     FROM LHS_ONCORE_PROD.sv_user_pcl_permission priv_check
     WHERE priv_check.function_name = 'CRPT-Subject Visits'
     AND priv_check.contact_id in (", contact_ids, ")") )

• 分批处理参数：当Contacts行数过多时，一次性传递大量参数可能触发驱动限制，可拆分批次查询后合并结果：

batch_size <- 100
batches <- split(Contacts$CONTACT_ID, ceiling(seq_along(Contacts$CONTACT_ID)/batch_size))
Access_list <- lapply(batches, function(batch) {
  qmarks <- paste(rep("?", length(batch)), collapse = ",")
  dbGetQuery(jdbcConnection,
             paste("SELECT priv_check.contact_id, priv_check.protocol_id
                   FROM LHS_ONCORE_PROD.sv_user_pcl_permission priv_check
                   WHERE priv_check.function_name = 'CRPT-Subject Visits'
                   AND priv_check.contact_id in (", qmarks, ")"),
             params = as.list(batch))
})
Access <- do.call(rbind, Access_list)

• 更换JDBC驱动版本：你提到有其他jar文件可选，尝试切换到不同版本的ojdbc（如ojdbc10），部分新版本驱动对批量参数的支持更完善。

• 验证SQL语法正确性：打印拼接后的SQL语句，手动检查是否符合Oracle语法规则，排除格式错误：

sql_query <- paste("SELECT priv_check.contact_id, priv_check.protocol_id
     FROM LHS_ONCORE_PROD.sv_user_pcl_permission priv_check
     WHERE priv_check.function_name = 'CRPT-Subject Visits'
     AND priv_check.contact_id in (", qmarks, ")")
print(sql_query)

内容的提问来源于stack exchange，提问作者Joe Crozier