9.5.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 |
|---|---|
|
|
A cursor that contains argument values to pass to the R function specified by the |
|
|
One of the following:
|
|
|
The name of a script in the OML4R script repository. |
Return Value
Function rqEval returns a table that has the structure specified by the OUT_QRY parameter value.
Examples
Example 9-18 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 OML4R script repository.
-- Create a script named myRandomRedDots2 and add it to the 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 9-19 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 9-20 Specifying PNG as the Output Table Definition
This example creates a script named PNG_Example and stores it in the 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)