Forum Stats

  • 3,750,548 Users
  • 2,250,192 Discussions
  • 7,867,003 Comments

Discussions

Non-numeric data frame in rqEval

3631507
3631507 Member Posts: 45
edited Mar 2, 2020 5:21AM in R Technologies

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?

Best Answer

  • Sherry Lamonica-Oracle
    Sherry Lamonica-Oracle Posts: 437 Employee
    edited Feb 24, 2020 1:21PM Accepted 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

    3631507

Answers

  • Sherry Lamonica-Oracle
    Sherry Lamonica-Oracle Posts: 437 Employee
    edited Feb 24, 2020 1:21PM Accepted 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

    3631507