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