9.6.2.5 rqTableEval2 Function
The function rqTableEval2 runs the uer-defined R function
in the script specified by the SCR_NAME parameter.
Pass data to the user-defined R function with the table name specified in the
INP_NAM parameter. Pass arguments to the user-defined R function with the
PAR_LST parameter.
You define the form of the returned value with the OUT_FMT
parameter.
Syntax
rqTableEval2(
INP_NAM VARCHAR2,
PAR_LST VARCHAR2,
OUT_FMT VARCHAR2,
SCR_NAME VARCHAR2,
SCR_OWNER VARCHAR2 DEFAULT NULL,
ENV_NAME VARCHAR2 DEFAULT NULL
)Parameters
Table 9-19 Parameters of the rqTableEval Function
| Parameter | Description |
|---|---|
|
|
The name of a table or view that specifies the data to pass to the R function
specified by the SCR_NAME parameter. If using a table or view owned by another
user, use the format |
|
|
A JSON string that contains additional parameters to pass to the user-defined R
function specified by the SCR_NAME parameter. Special control arguments, which
start with For example, to specify converting the one column input data.frame to a vector, use:
See also: Special Control Arguments. |
|
|
The format of the output returned by the function. It can be one of the following:
See also: Output Formats. |
|
|
The name of a user-defined R function in the OML4R script repository. |
|
|
The owner of the R script. The default value is |
|
|
The name of the conda environment that should be used when running the named user-defined R function. |
Return Value
Function rqTableEval2 returns a table that has the structure
specified by the OUT_FMT parameter value.
Examples
This example creates a function and stores it as the script buildLM in the repository.
Example 9-38 Using the rqTableEval2 Function
In a PL/SQL block, creates the R function buildLM and stores in the script
repository with the name buildLM, overwriting any existing user-defined R
function stored in the script repository with the same name.
BEGIN
sys.rqScriptCreate('buildLM',
'function(dat, dsname) {
mod <- lm(Petal.Length~Petal.Width, dat)
ore.save(mod, name=dsname, overwrite=TRUE)
plot(predict(mod), dat$Petal.Length, pch=21, bg=c("red","blue"), xlab = "Predicted Values", ylab = "Observed Values")
abline(a = 0, b = 1, lwd=2, col = "green")
return(data.frame(Coef=mod$coef))}',
v_global => FALSE,
v_overwrite => TRUE);
END;
/Example 9-39 JSON Output
The INP_NAM argument passes the 'IRIS' table to the user defined function. The PAR_LST argument specifies using LOW service level with the special control argument ore_service_level. In the OUT_FMT argument, the string 'JSON', specifies that the table returned contains a CLOB that is a JSON string. The scr_name parameter specifies the buildLM function in the script repository as the R function to call. The JSON output is a CLOB. You can call set long [length] to get more output.
%script
set long 500
SELECT * FROM table(rqTableEval2(
inp_nam => 'IRIS',
par_lst => '{"dsname":"ds-1", "ore_service_level":"LOW"}',
out_fmt => 'JSON',
scr_name => 'buildLM'));The result is:
---------------------------
NAME VALUE
[{"_row":"(Intercept)","Coef":1.0836},{"_row":"Petal.Width","Coef":2.2299}]
---------------------------Example 9-40 PNG Output
The
par_lst argument specifies using LOW service level with
the special control argument ore_service_level. In the out_fmt argument,
the string 'PNG' specifies to include images in the BLOB column., The
scr_name parameter specifies the buildLM function in the script
repository as the R function to
call.
%script
set long 500
SELECT * FROM table(rqTableEval2(
inp_nam => 'IRIS',
par_lst => '{"dsname":"ds-1", "ore_graphics_flag":true, "ore_service_level":"LOW"}',
out_fmt => 'PNG',
scr_name => 'buildLM'));The result is:
---------------------------
NAME ID VALUE IMAGE
1 89504E470D0A1A0A0000000D49484452000001E0000001E0080Note:
Here, only a portion of the output is shown. To determine the length of the output use the parameterset long
[length]Example 9-41 Relational Output
The inp_nam argument passes the 'IRIS' table to the user defined function. The par_lst argument specifies using LOW service level with the special control argument ore_service_level. In the out_fmt argument, specifies the column names and data types of the table returned by the function. The scr_name parameter specifies the buildLM function in the script repository as the R function to call.
%script
SELECT * FROM table(rqTableEval2(
inp_nam => 'IRIS',
par_lst => '{"dsname":"ds-1", "ore_service_level":"LOW"}',
out_fmt => '{"Coef":"number"}',
scr_name => 'buildLM'));The result is:
Coef
1.0836
2.2299Parent topic: Embedded R Execution Functions (Autonomous Database)