A.2 rqEval Function

The rqEval function executes the R function in the script specified by the EXP_NAM parameter. You can pass arguments to the R function with the PAR_CUR parameter.

The rqEval function does not automatically receive any data from the database. The R function generates the data that it uses or it explicitly retrieves it from a data source such as Oracle Database, other databases, or flat files.

The R function returns an R data.frame object, which appears as a SQL table in the database. You define the form of the returned value with the OUT_QRY parameter.

Syntax

rqEval (
     PAR_CUR     REF CURSOR     IN
     OUT_QRY     VARCHAR2       IN)
     EXP_NAM     VARCHAR2       IN)

Parameters

Parameter	Description
PAR_CUR	A cursor that contains argument values to pass to the R function specified by the EXP_NAME parameter.
OUT_QRY	One of the following: NULL, which returns a serialized object that can contain both data and image objects. A SQL SELECT statement that specifies the column names and data types of the table returned by rqEval. Any image data is discarded. You can provide a prototype row using the dual dummy table or you can base the SELECT statement on an existing table or view. The R function must return a data.frame. The string 'XML', which specifies that the table returned contains a CLOB that is an XML string. The XML can contain both structured data and images, with structured or semi-structured R objects first, followed by the image or images generated by the R function. Images are returned as a base 64 encoding of the PNG representation. The string 'PNG', which specifies that the table returned contains a BLOB that has the image or images generated by the R function in PNG format.
EXP_NAM	The name of a script in the Oracle R Enterprise R script repository.

Return Value

Function rqEval returns a table that has the structure specified by the OUT_QRY parameter value.

Examples

Example A-2 Using rqEval

This example creates the script myRandomRedDots2. The value of the first parameter to rqEval is NULL, which specifies that no arguments are supplied to the function myRandomRedDots2. The value of second parameter is a string that specifies a SQL statement that describes the column names and data types of the data.frame returned by rqEval. The value of third parameter is the name of the script in the Oracle R Enterprise R script repository.

-- Create a script named myRandomRedDots2 and add it to the R script repository.
-- Specify that the script is private and to overwrite a script with the same name.
BEGIN
  sys.rqScriptCreate('myRandomRedDots2',
    'function(divisor = 100, numDots = 100) {
       id <- 1:10
       plot(1:numDots, rnorm(numDots), pch = 21, bg = "red", cex = 2 )
       data.frame(id = id, val = id / divisor)}',
       v_global => FALSE,
       v_overwrite => TRUE);
END;
/

SELECT *
  FROM table(rqEval(NULL, 'SELECT 1 id, 1 val FROM dual', 'myRandomRedDots2'));

In Oracle SQL Developer, the results of the SELECT statement are:

        ID        VAL
---------- ----------
         1        .01 
         2        .02 
         3        .03 
         4        .04 
         5        .05 
         6        .06 
         7        .07 
         8        .08 
         9        .09 
        10         .1 
 
 10 rows selected 

Example A-3 Passing Arguments to the R Function invoked by rqEval

This example provides arguments to the R function by specifying a cursor as the first parameter to rqEval. The cursor specifies multiple arguments in a single row of scalar values.

SELECT *
  FROM table(rqEval(cursor(SELECT 50 "divisor", 500 "numDots" FROM dual),
    'SELECT 1 id, 1 val FROM dual',
    'myRandomRedDots2'));

In Oracle SQL Developer, the results of the SELECT statement are:

        ID        VAL
---------- ----------
         1        .02 
         2        .04 
         3        .06 
         4        .08 
         5         .1 
         6        .12 
         7        .14 
         8        .16 
         9        .18 
        10         .2 
 
 10 rows selected 

Example A-4 Specifying PNG as the Output Table Definition

This example creates a script named PNG_Example and stores it in the Oracle R Enterprise R script repository. The invocation of rqEval specifies an OUT_QRY value of 'PNG'.

BEGIN
  sys.rqScriptDrop('PNG_Example');
  sys.rqScriptCreate('PNG_Example',
    'function(){
      dat <- data.frame(y = log(1:100), x = 1:100)
      plot(lm(y ~ x, dat))
      }');
END;
/
SELECT *
  FROM table(rqEval(NULL,'PNG','PNG_Example'));

In Oracle SQL Developer, the results of the SELECT statement are:

NAME     ID  IMAGE
------ ----  ------
          1  (BLOB)
          2  (BLOB)
          3  (BLOB)
          4  (BLOB)