Non-numeric data frame in rqEval

3631507

Am I correct that rqEval cannot accept non-numeric data frames in the 'SELECT ..'? I tried this

begin

sys.rqScriptCreate('Test',

'function() {

library(ROracle)

drv <- dbDriver("Oracle")

con <- dbConnect(drv, username = "rquser", password="password",  dbname = "ORCLPDB", prefetch = FALSE,external_credentials = FALSE)

Industry <- "Text"

data.frame(Industry = Industry)

}',

v_overwrite => TRUE);

end;

/

select * from table(rqEval(NULL,'select 1 "Industry" from dual', 'Test'));

and got the error

ORA-20000: RQuery error

output data.frame contains unsupported types

When "Text" is changed to a number like

Industry <- 25

the script runs fine with 25 for the Industry column.

If rqEval cannot accept non-numerics, what alternative is there to insert non-numeric results into an Oracle table?

Sherry Lamonica-Oracle

rqEval can accept non-numeric types as input.

In your example, 'select 1 "Industry" forces a returned numeric value.  It fails because the data returned contains 1 non-numeric column. The output doesn't match the specification in the SQL provided, and an error is returned.

Instead, use cast to return as varchar as follows:

SQL> select * from table(rqEval(

        NULL,

        'select cast(''Industry'' as varchar2(8)) "Industry" from dual',

       'Test'));

Returns:

1 Text

Sherry Lamonica-Oracle

rqEval can accept non-numeric types as input.

In your example, 'select 1 "Industry" forces a returned numeric value.  It fails because the data returned contains 1 non-numeric column. The output doesn't match the specification in the SQL provided, and an error is returned.

Instead, use cast to return as varchar as follows:

SQL> select * from table(rqEval(

        NULL,

        'select cast(''Industry'' as varchar2(8)) "Industry" from dual',

       'Test'));

Returns:

1 Text