RORACLE passing a BOOLEAN parameter to a stored procedure using oracleProc

Question

Using ROracle package in R Studio 2021.09.0

I have a stored procedure

PROCEDURE A_TEST_RORACLE_IN_BOOLEAN_OUT_CLOB (v_bool IN BOOLEAN, v_resp OUT CLOB) IS

BEGIN
  
  IF (v_bool=TRUE) THEN
    v_resp := 'The value passed is TRUE';
  ELSIF (v_bool=FALSE) THEN
     v_resp := 'The value passed is FALSE';
  ELSE
    v_resp := 'The value passed is NULL';
  END IF;
 
  
END A_TEST_RORACLE_IN_BOOLEAN_OUT_CLOB;

I have a functioning driver/con etc - have called similar test procedures using INTEGER IN and CLOB OUT vars ... hung up on the BOOLEAN thing.

I call it using these lines in an R script.

        v_answer = FALSE  
        df_test <- data.frame(v_bool=v_answer,v_resp=as.character(NA),stringsAsFactors=FALSE)  

        attr(df_test$v_bool,   "ora.parameter_name") <- "oompalumpa"  
        attr(df_test$v_bool,   "ora.parameter_mode") <- "IN"  
        attr(df_test$v_bool,   "ora.type") <- "BOOLEAN"  
        attr(df_test$v_resp,   "ora.parameter_name") <- "jazzy"          
        attr(df_test$v_resp,   "ora.parameter_mode") <- "OUT"  
        attr(df_test$v_resp,   "ora.type") <- "CLOB"  

        v_test_smpl_mv <- 'BEGIN TESTSCHEMA.TESTPACKAGE.A_TEST_RORACLE_IN_BOOLEAN_OUT_CLOB(:oompalumpa,:jazzy);END;'

        v_result <- oracleProc(con,v_test_smpl_mv,df_test)  

get response

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

have tried:

df_test <- data.frame(v_bool=as.logical(v_answer),v_resp=as.character(NA),stringsAsFactors=FALSE) 

and

leaving out the BOOLEAN specific ora.type attr() and setting the ora.type attr() to other types (CHAR,VARCHAR,RAW) and changing the value of v_answer

v_answer = 'FALSE'

... all roads lead to silence or ORA- errors.

this ad-hoc pl/sql call returns 'The value passed is NULL' as expected.

DECLARE

 v_clob CLOB;
 v_char boolean;
 
BEGIN WRM_ADMIN.WRM_MAINT_DATA_LOAD_02.A_TEST_RORACLE_IN_BOOLEAN_OUT_CLOB(v_char,v_clob);
DBMS_OUTPUT.PUT_LINE(v_clob);

END;

Left to conclude that BOOLEAN parameters are a bad idea, or at the very least unsupported in OCI/RORACLE ... but I cannot figure out where that is documented.

I have looked here:

https://cran.r-project.org/web/packages/ROracle/ROracle.pdf

And googled the google ... nothing comes back that helps me.

Thanks in advance for any definitive guidance you can offer.

Answer 1

Contacted the maintainer of the package @ Oracle and got a report back that they will add BOOLEAN support to a future release of ROracle; they were extremely responsive and helpful.

In the meantime ... presuming you already have other code that uses BOOLEAN values and you need a quick workaround.

-- change the in parameters to char 
PROCEDURE A_TEST_RORACLE_IN_CHARBOOL_OUT_CLOB (v_bl_flg IN CHAR default 'F', v_resp OUT CLOB) IS
-- ADD THIS vvv 
 v_bool BOOLEAN;

BEGIN
-- ADD THIS vvv

  IF (v_bl_flg = 'T') THEN
    v_bool := TRUE;
  ELSIF (v_bl_flg = 'F') THEN
     v_bool := FALSE
  END IF;

 -- ADD THIS ^^^^
   
  IF (v_bool=TRUE) THEN
    v_resp := 'The value passed is TRUE';
  ELSIF (v_bool=FALSE) THEN
     v_resp := 'The value passed is FALSE';
  ELSE
    v_resp := 'The value passed is NULL';
  END IF;
 
  
END A_TEST_RORACLE_IN_CHARBOOL_OUT_CLOB;

and adjust the R-part accordingly

        v_answer = 'F'  
        df_test <- data.frame(v_bl_flg=as.character(v_answer),v_resp=as.character(NA),stringsAsFactors=FALSE)  

     ##   attr(df_test$v_bl_flg,   "ora.parameter_name") <- "oompalumpa"  
        attr(df_test$v_bl_flg,   "ora.parameter_mode") <- "IN"  
        attr(df_test$v_bl_flg,   "ora.type") <- "CHAR"  
     ##   attr(df_test$v_resp,   "ora.parameter_name") <- "jazzy"          
        attr(df_test$v_resp,   "ora.parameter_mode") <- "OUT"  
        attr(df_test$v_resp,   "ora.type") <- "CLOB"  

        v_test_smpl_mv <- 'BEGIN TESTSCHEMA.TESTPACKAGE.A_TEST_RORACLE_IN_CHARBOOL_OUT_CLOB(:oompalumpa,:jazzy);END;'

        v_result <- oracleProc(con,v_test_smpl_mv,df_test)  

Above is close but has not been tested. May require adjustment. I've had more trouble suggesting the parameter name in an ATTR() call than just leaving it out - hence the commented out ATTR() lines for ora.parameter_name.

Hope this helps someone who runs across this in the future.