Forum Stats

  • 3,768,917 Users
  • 2,252,874 Discussions
  • 7,874,797 Comments

Discussions

ROracle 1.3.2 - Unable to insert xml string of length > 32767 into oracle table with xmltype column

User_M1BV3
User_M1BV3 Member Posts: 1 Green Ribbon
edited Jul 31, 2021 5:55PM in R Technologies
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

Answers