Skip to Main Content

Data Science & Machine Learning

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

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

User_WANWTMay 10 2022 — edited May 10 2022

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

Comments

Processing

Post Details

Added on May 10 2022
0 comments
211 views