R data frame to Oracle SQL table: Could not alloca

2019-08-02 23:52发布

问题:

I want to insert a data frame from R into an existing table in my Oracle database using this code:

sqlSave(connection, df, tablename = "oracle_table", append = TRUE)

I get the error could not allocate memory. I tried inserting only one line, but the error still showed up. The type of some fields in the Oracle target table are memo.

How can I insert my df in the oracle table?

Structure of R data frame:

$a: num
$b: chr

Structure of target table in Oracle database:

a: number(10,0)
b: CLOB

Example data set:

b <- c("Lorem ipsum dolor sit amet, consetetur sadipscing elitr, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, sed diam voluptua Lorem ipsum dolor sit amet, consetetur sadipscing elitr, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, sed diam voluptua Lorem ipsum dolor sit amet, consetetur sadipscing elitr, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, sed diam voluptua Lorem ipsum dolor sit amet, consetetur sadipscing elitr, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, sed diam voluptua Lorem ipsum dolor sit amet, consetetur sadipscing elitr, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, sed diam voluptua Lorem ipsum dolor sit amet, consetetur sadipscing elitr, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, sed diam voluptua","Lorem ipsum dolor sit amet, consetetur sadipscing elitr, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, sed diam voluptua Lorem ipsum dolor sit amet, consetetur sadipscing elitr, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, sed diam voluptua Lorem ipsum dolor sit amet, consetetur sadipscing elitr, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, sed diam voluptua At vero eos et accusam et justo duo dolores et ea rebum"," Stet clita kasd gubergren, no sea takimata sanctus est Lorem ipsum dolor sit amet"," Lorem ipsum dolor sit amet, consetetur sadipscing elitr, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, sed diam voluptua"," At vero eos et accusam et justo duo dolores et ea rebum Lorem ipsum dolor sit amet, consetetur sadipscing elitr, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, sed diam voluptua Lorem ipsum dolor sit amet, consetetur sadipscing elitr, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, sed diam voluptua Lorem ipsum dolor sit amet, consetetur sadipscing elitr, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, sed diam voluptua"," Stet clita kasd gubergren, no sea takimata sanctus est Lorem ipsum dolor sit amet"," Lorem ipsum dolor sit amet, consetetur sadipscing elitr, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, sed diam voluptua", " Stet clita kasd gubergren, no sea takimata sanctus est Lorem ipsum dolor sit amet Lorem ipsum dolor sit amet, consetetur sadipscing elitr, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, sed diam voluptua Lorem ipsum dolor sit amet, consetetur sadipscing elitr, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, sed diam voluptua","Duis autem vel eum iriure dolor in hendrerit in vulputate velit esse molestie consequat, vel illum dolore eu feugiat nulla facilisis at vero eros et accumsan et iusto odio dignissim qui blandit praesent luptatum zzril delenit augue duis dolore te feugait nulla facilisi"," Lorem ipsum dolor sit amet, consectetuer adipiscing elit, sed diam nonummy nibh euismod tincidunt ut laoreet dolore magna aliquam erat volutpat")
df <- data.frame(A = seq(1:length(b)), B = b, C = tolower(gsub("[[:space:]]|[[:punct:]]", "", b)))
sqlSave(connection, df, tablename = "ORACLE_TABLE", append = TRUE)

Would it be easier just to save the entire df as a table in my Oracle db without creating the Oracle table first? How could I do this in R?

回答1:

I just found out how to put a data frame from R into a data base in Oracle using ROracle:

require(ROracle)
Con=dbConnect(dbDriver("Oracle"),"IP/SID",username="xxx",password="xxx")
dbWriteTable(Con, "ORACLE_TABLE", df, row.names = TRUE, overwrite = TRUE)

This is a compromise solution, but at least I now have "inserted" my df into the db.