3 Replies Latest reply: Oct 22, 2012 1:17 PM by Sherry Lamonica-Oracle RSS

    Pass database table to R function

    Artem Khodyaev
      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-Oracle
          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
            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-Oracle
              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