library(ROracle)
con <- dbConnect(drv,
username,
password,
dbname = connect.string)
id <- 1
lstr1 <- paste(rep('a', 35000), collapse="")
xml_str_data <- paste0('<x>', lstr1, '</x>')
df <- data.frame(id, xml_str_data, stringsAsFactors = FALSE)
# The table1 has column name xml_str_data of datatype xmltype.
sql_query <- "insert into db1.table1 (id, xml_str_data) values (:id, :xml_str_data)"
res <- dbGetQuery(con, sql_query, data = df)
# I get the error shown below
Error in .oci.GetQuery(conn, statement, data = data, prefetch = prefetch, :
ORA-01461: can bind a LONG value only for insert into a LONG column
# If I call the xmltype function, I get the same error as shown below.
sql_query <- "insert into db1.table1 (id, xml_str_data) values (:id, xmltype(:xml_str_data))"
res <- dbGetQuery(con, sql_query, data = df)
Error in .oci.GetQuery(conn, statement, data = data, prefetch = prefetch, :
ORA-01461: can bind a LONG value only for insert into a LONG column
# If I set the ora.type attribute as clob, I get the same error again
attr(df$xml_str_data, "ora.type") <- "clob"
sql_query <- "insert into db1.table1 (id, xml_str_data) values (:id, xmltype(:xml_str_data))"
res <- dbGetQuery(con, sql_query, data = df)
Error in .oci.GetQuery(conn, statement, data = data, prefetch = prefetch, :
ORA-01461: can bind a LONG value only for insert into a LONG column
The code above only fails when length of xml_str_data is > 32767. If it's length is <=32767, I am able to insert the xml string as xmltype into the table.
The ROracle 1.3.1 documentation indicates the attributes should be used to map to CLOB & other datatypes. This didn't work for me as shown in the code above.
So, my question is how do I insert xml strings of length > 32767 into an oracle table with xmltype column using ROracle.
Best Regards,
Mohan