Skip to Main Content
Forums

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!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Non-numeric data frame in rqEval

3631507Feb 7 2020 — edited Mar 2 2020

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?
This post has been answered by Sherry Lamonica-Oracle on Feb 24 2020
Jump to Answer

Comments

Answer

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

Marked as Answer by 3631507 · Sep 27 2020
1 person found this helpful
1 - 1

Post Details

Added on Feb 7 2020
#r-technologies
1 comment
326 views