将数据插入Oracle表 [英] Inserting Data into an Oracle Table

问题描述

我对R非常陌生，因此请原谅任何明显或天真的错误.我需要将R中的多行数据插入到Oracle数据库表中.

I am very new to R, so please forgive any obvious or naive errors. I need to insert multiple rows of data from R into an Oracle database table.

制作数据帧(我在脚本的前面已经建立了RJDBC连接):

Make the data frame (I have made the RJDBC connection earlier in the script):

df <- data.frame("field_1" = 1:2, "field_2" = c("f","k"), "field_3"= c("j","t"))

此代码运行无错误，但仅将第一行插入到表中:

This code runs without error, but inserts only the first row into the table:

insert <- sprintf("insert into temp_r_test_u_suck values (%s')", 
                  apply(df, 1, function(i) gsub(" ", "", paste("'", i, collapse="',"), fixed = TRUE)))
dbSendUpdate(con, insert)

此代码运行:

insert <- sprintf("into temp_r_test_u_suck values (%s')", 
                  apply(df, 1, function(i) gsub(" ", "", paste("'", i, collapse="',"), fixed = TRUE)))
insert_all <- c("insert all", insert, "select * from dual")
dbSendUpdate(con, insert_all)

但是给我这个错误:

Error in .local(conn, statement, ...) : 
  execute JDBC update query failed in dbSendUpdate (ORA-00905: missing keyword

这两个查询在Oracle中都是独立工作的.我究竟做错了什么?

Both of the queries work on their own in Oracle. WHAT am I doing wrong?

谢谢！

推荐答案

dbGetQuery ， dbSendQuery ， dbSendUpdate 不支持多个SQL语句.电话.您需要为每个语句遍历它们.因此，为什么只执行第一个语句.要解决此问题，请在 apply 中扩展匿名函数以调用 dbSendUpdate :

Multiple SQL statements are not supported in dbGetQuery, dbSendQuery, dbSendUpdate calls. You need to iterate through them for each statement. Hence, why only the first statement processes. To resolve, extend the anonymous function inside apply to call dbSendUpdate:

apply(df, 1, function(i) {
      # BUILD SQL STATEMENT
      insert <- sprintf("insert into temp_r_test_u_suck values (%s')", 
                        paste0("'", i, collapse="',"))

      # RUN QUERY
      dbSendUpdate(con, insert)
})

但是，如中所述，RJDBC通过使用 dbSendUpdate 支持参数化扩展了DBI标准.用于批量插入的rForge文档，无需迭代连接字符串.

However, RJDBC extends the DBI standard by supporting parameterization with dbSendUpdate as mentioned in rForge docs for bulk-inserts with no need for iteratively concatenating strings.

dbSendUpdate(conn，statement，...)

该函数类似于dbSendQuery，但可用于DBML语句，因此不会返回结果集.它比dbSendQuery更有效.此外，截至RJDBC 0.2-9，它在准备好的语句中支持向量，从而允许批量插入.

dbSendUpdate(conn, statement, ...)

This function is analogous to dbSendQuery, but works with DBML statements and thus doesn't return a result set. It is more efficient than dbSendQuery. In addition, as of RJDBC 0.2-9 it supports vectors in prepared statements which allows bulk-inserts.

# ALL CHARACTER DATAFRAME
df <- data.frame(field_1=as.character(1:2), field_2=c("f","k"), field_3=c("j","t"), 
                 stringsAsFactors=FALSE)

# PREPARED STATEMENT
sql <- "insert into temp_r_test_u_suck values (?, ?, ?)"

# RUN QUERY
dbSendUpdate(con, sql, df$field_1, df$field_2, df$field_3)

这篇关于将数据插入Oracle表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！