Executing a stored procedure with m_ref_cursor parameter in R

Question

I'm trying to execute a stored procedure with a REF CURSOR parameter.

My stored procedure is something like:

PROCEDURE procName(p_in_str IN VARCHAR2, p_out_result IN OUT SYS_REFCURSOR) ...

And I have based on these post to try to execute it from R:

After many errors I have reached a point where I can not move forward. Thes are my current approaches

Attempt 1

# Not error but not return data
temp_output1 <- data.frame(p_in_str = "My string", stringsAsFactors = FALSE)
attr(temp_output1$p_in_str, "ora.parameter_name") <- "p_in_str";
attr(temp_output1$p_in_str, "ora.parameter_mode") <- "IN";
rs <- oracleProc(con, "DECLARE c SYS_REFCURSOR;
                 BEGIN 
                   procname(:p_in_str, c);
                 END;", data = temp_output1)

Attempt 2

# Wrong number or types of arguments 
temp_output2 <-data.frame(p_in_str = "My string", 
                          p_out_ref = "p_out_ref", 
                          stringsAsFactors = FALSE)
attr(temp_output2$p_in_str, "ora.parameter_name") <- "p_in_str";
attr(temp_output2$p_in_str, "ora.parameter_mode") <- "IN";
attr(temp_output2$p_out_ref, "ora.parameter_name") <- "p_out_ref";
attr(temp_output2$p_out_ref, "ora.parameter_mode") <- "IN OUT";
attr(temp_output2$p_out_ref, "ora.type") <- "SYS_REFCURSOR"
rs <- oracleProc(con, "BEGIN 
                         procname(:p_in_str, :p_out_ref);
                       END;", data = temp_output2)

Error in .oci.oracleProc(conn, statement, data = data, prefetch = prefetch,
: ORA-06550: line 2, column 18: PLS-00306: wrong number or types of arguments in call to 'procname' ORA-06550: line 2, column 18: PL/SQL: Statement ignored

Attempt 3

# Bind data does not match bind specification
temp_output3 <-data.frame(p_in_str = "My string", 
                          p_out_ref = as.character(NA), 
                          stringsAsFactors = FALSE)
attr(temp_output3$p_in_str, "ora.parameter_name") <- "p_in_str";
attr(temp_output3$p_in_str, "ora.parameter_mode") <- "IN";
attr(temp_output3$p_out_ref, "ora.parameter_name") <- "p_out_ref";
attr(temp_output3$p_out_ref, "ora.parameter_mode") <- "IN OUT";
attr(temp_output3$p_out_ref, "ora.type") <- "m_ref_cursor"

rs <- oracleProc(con, "DECLARE c SYS_REFCURSOR;
                 BEGIN 
                  procname(:p_in_str, c);
                 END;", data = temp_output3)

Error in .oci.oracleProc(conn, statement, data = data, prefetch = prefetch, : bind data does not match bind specification

Since you have two statements, try running the declare line first then `oracleProc`: `dbSendQuery(con, 'DECLARE c SYS_REFCURSOR'); rs <- oracleProc(con, 'BEGIN procname(:p_in_str, c); END;", data = temp_output1)` — Parfait, Jun 05 '19 at 21:01
Thanks Parfait, but it doesn't work. When I send the declare it returns an error: `Error in .oci.SendQuery (conn, statement, data = data, prefetch = prefetch,:` — Juan Antonio Roldán Díaz, Jun 06 '19 at 08:49
Then I get `PLS-00201: identifier 'c' must be declared` and if I remove c `PLS-00306: wrong number or types of arguments ...` — Juan Antonio Roldán Díaz, Jun 06 '19 at 14:22
In error above, you cut off the message. What does it say after the colon? — Parfait, Jun 06 '19 at 14:38

score 1 · Answer 1 · answered Mar 29 '22 at 20:00

I had a similar problem yesterday, but the stored procedure only had IN and OUT:

PROCEDURE procName(p_in_str IN VARCHAR2,
                   p_out_result OUT SYS_REFCURSOR) ...

For SYS_REFCURSOR types you need to use NA avoiding any other function, e.g., as.character(), as.numeric()

temp_output3 <- data.frame(p_in_str = "My string",
                           p_out_ref = NA, # cursor var definition
                           stringsAsFactors = FALSE)

You must define the ora.type of your OUT variable as "cursor":

attr(temp_output3$p_in_str, "ora.parameter_name") <- "p_in_str";
attr(temp_output3$p_in_str, "ora.parameter_mode") <- "IN";
attr(temp_output3$p_out_ref, "ora.parameter_name") <- "p_out_ref";
attr(temp_output3$p_out_ref, "ora.parameter_mode") <- "IN OUT";
attr(temp_output3$p_out_ref, "ora.type") <- "cursor"

And in the procedure query you must specify both bindings. There is not need to DECLARE because the attributes are provided by the dataframe temp_output3

rs <- oracleProc(con, "
                 BEGIN 
                  procname(:p_in_str, :p_out_ref);
                 END;",
                 data = temp_output3)

Executing a stored procedure with m_ref_cursor parameter in R

1 Answers1