ROracle 1.3-2 R 4.1.2 - Binding BLOB/CLOB to arguments of ROracle::oracleProc()

User_WANWT

Hi, 

I would need to store BLOB/CLOB with larger raw/character data by passing them to the oracle procedure from R.

The documentation of ROracle indicates it should be possible. I can pass the data directly by using dbWritetable 

when setting attr(in.df$msg_att, "ora.type") <- "BLOB" of the input dataframe. I suppose the same 

logic should apply in oracleProc. Unfortunately, it does not work. Setting it to BLOB binds 

the NULL value within the db procedure. When not set, it can pass maximum 32767 Bytes.  

Here is the script:

ch <- db_connect("dm0_cb_owner")

ch <- db_connect_oci()

ch <- db_connect(ROracle::Oracle(), user, password)

dbGetQuery(ch, "CREATE SEQUENCE ATT_ID_SEQ")

dbGetQuery(

 ch,

 "CREATE TABLE T_ATT_TEST

(msg_id NUMBER,

 att_id NUMBER GENERATED BY DEFAULT AS IDENTITY,

 msg_att CLOB)"

)

dbGetQuery(

 ch,

 "CREATE OR REPLACE PROCEDURE P_ATT_TEST

(msg_id IN INTEGER, att_id OUT integer, msg_att IN BLOB)

IS

BEGIN

insert into T_ATT_TEST(msg_id, att_id, msg_att)

values(msg_id, att_id_seq.nextval, msg_att)

returning att_id into att_id;

END;"

)

raw.lst <- vector("list", 1)

raw.lst[[1L]] <- charToRaw(paste(rep('x', 32767), collapse = ""))

in.df <- data.frame(msg_id = 1,

 att_id = as.integer(NA),

 stringsAsFactors = F)

in.df$msg_att <- raw.lst

attr(in.df$msg_id, "ora.parameter_name") <- "msg_id"

attr(in.df$msg_id,  "ora.parameter_mode") <- "IN"

attr(in.df$msg_att, "ora.parameter_name") <- "msg_att"

attr(in.df$msg_att,  "ora.parameter_mode") <- "IN"

#attr(in.df$msg_att, "ora.type") <- "BLOB"

attr(in.df$att_id, "ora.parameter_name") <- "att_id"

attr(in.df$att_id, "ora.parameter_mode") <- "OUT"

att_id <- oracleProc(ch, 'BEGIN P_ATT_TEST(

:msg_id,

:att_id,

:msg_att

 ); END;', in.df)

dbCommit(ch)

res <- dbReadTable(ch, "T_ATT_TEST")

str(res)

# remove created objects

dbGetQuery(ch, "DROP SEQUENCE ATT_ID_SEQ")

dbGetQuery(ch, "DROP TABLE T_ATT_TEST")

dbGetQuery(ch, "DROP PROCEDURE P_ATT_TEST")

dbDisconnect(ch)

Can somebody help me with an example of binding BLOB/CLOB to oracleProc?

Thank you and best regards

Ondrej