This discussion is archived
3 Replies Latest reply: Oct 22, 2012 11:17 AM by Sherry LaMonica RSS

Pass database table to R function

artem khodyaev Newbie
Currently Being Moderated
Hello,

I have a problem with passing database table as parameter to R function.

I have a function:

function(x, y, z,levels=20,filename=''''){
library ( lattice )
grid <- data.frame ( x = x, y = y, z = z)
png ( file = "/oracle/image.png")
print ( levelplot ( z~x*y, data = grid, cuts = levels, contour = TRUE, col.regions = topo.colors, labels = TRUE ) )
dev.off()

And when I trying to execute it:

select *
from table(rqTableEval(
cursor ( select x, y, parameter_value as z from geological_model_grid),
NULL,
NULL,
'Example5'));

I have an error:
Error in eval(expr, envir, enclos) : object 'z' not found
ORA-06512: на "RQSYS.RQTABLEEVALIMPL", line 60

I can't understand how to pass query result to R function. Should I use columns alias in query to match it names to R function parameters names?
  • 1. Re: Pass database table to R function
    Sherry LaMonica Journeyer
    Currently Being Moderated
    Table data can be passed to an R function only as the first argument. It's automatically converted to a data frame with matching column names of the table. So, you can do as follows instead:

    function(grid, levels=20,filename=''''){
    library ( lattice )
    png ( file = "/oracle/image.png")
    print ( levelplot ( z~x*y, data = grid, cuts = levels, contour = TRUE, col.regions = topo.colors, labels = TRUE ) )
    dev.off()
    ... ...

    select *
    from table(rqTableEval(
    cursor ( select x as "x", y as "y", parameter_value as "z" from geological_model_grid),
    NULL,
    NULL,
    'Example5'));


    The first parameter "grid" in the function will contain a data.frame with columns x, y, and z and corresponds to the cursor provided:

    cursor ( select x as "x", y as "y", parameter_value as "z" from geological_model_grid)

    The "" is required for case sensitivity (to retain lower-case), otherwise, the database will covert to upper-case. You can use u pper-case for the R function parameter name to avoid adding "" upon select.
  • 2. Re: Pass database table to R function
    artem khodyaev Newbie
    Currently Being Moderated
    Thank you Sherry!

    What about second, third and other function parameters? How to pass it?

    In my case "levels" and "filename" parameters.


    When I try to execute this query:
    select *
    from table(rqTableEval(
    cursor ( select x as "x", y as "y", parameter_value as "z" from geological_model_grid),
    cursor (select 10 as "levels", '/oracle/image.png' AS "filename" from dual),
    NULL,
    'Example5'));

    I have an error:
    ORA-01007: variable not in select list

    Edited by: artem.khodyaev on 20.10.2012 14:03
  • 3. Re: Pass database table to R function
    Sherry LaMonica Journeyer
    Currently Being Moderated
    The Embedded R Scripts Training slides provide examples on passing the second argument:

    http://www.oracle.com/technetwork/database/options/advanced-analytics/r-enterprise/ore-trng4-embeddedrscripts-1501638.pdf

    See pages 35-44 for details. The third argument is the output table definition, which specifies the format of the result.

    Hope this helps,

    Sherry

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points