rqIndexEval2 Function

9.6.2.8 rqIndexEval2 Function

The function rqIndexEval2 when used in Oracle Autonomous Database, runs a user-defined R function multiple times in R engines spawned by the database environment.

You can pass arguments to the user-defined R function with the PAR_LST parameter. Additional arguments can be passed to the parameter PAR_LST such as ore_parallel_flag, ore_service_level, etc. The boolean argument ore_parallel_flag, which has a default value of false, runs the user-defined R function with data parallelism. Different levels of performance and concurrency in Autonomous Database can be controlled by the argument ore_service_level, which has a default service level of LOW. See also: Special Control Arguments.

Syntax

rqIndexEval2(
    PAR_LST VARCHAR2,
    OUT_FMT VARCHAR2,
    TIMES_NUM NUMBER,
    SCR_NAME VARCHAR2,
    SCR_OWNER VARCHAR2 DEFAULT NULL,
    ENV_NAME VARCHAR2 DEFAULT NULL
)

Parameters

Parameter	Description
`PAR_LST`	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 `ore`, are not passed to the function specified by `SCR_NAME`, but instead control what happens before or after the invocation of the function. See also: Special Control Arguments.
`OUT_FMT`	The format of the output returned by the function. It can be one of the following: A JSON string that specifies the column names and data types of the table returned by the function. Any image data is discarded. The string `'JSON'`, which specifies that the table returned contains a CLOB that is a JSON string. 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. The string `'PNG'`, which specifies that the table returned contains a BLOB that has the image or images generated by the R function. Images are returned as a base 64 encoding of the PNG representation. See also: Output Formats.
`TIMES_NUM`	The number of times to run the R script.
`SCR_NAME`	The name of a user-defined R function in the OML4R script repository.
`SCR_OWNER`	The owner of the registered R script. The default value is `NULL`. If `NULL`, will search for the R script in the user’s script repository.
`ENV_NAME`	The name of the conda environment that should be used when running the named user-defined R function.

Example

The PL/SQL block, creates the script computeMean and add it to the script repository. Specify that the script is private and overwrite the script with the same name.

BEGIN
    sys.rqScriptCreate('computeMean',
        'function(idx, rseed){
            set.seed(rseed)
            x <- round(runif(100,2,10),4)
            return(mean(x))}',
        v_global => FALSE,
        v_overwrite => TRUE);
END;
/

The result is:

PL/SQL procedure successfully completed.


---------------------------

Example 9-49 JSON Output

Run the Select statement to get a JSON output.

%script

SELECT * FROM table(rqIndexEval2(
        par_lst => '{"rseed":99, "ore_parallel_flag":true, "ore_service_level":"MEDIUM"}',
        out_fmt => 'JSON',
        times_num => 5,
        scr_name => 'computeMean'));

The result is:

NAME   VALUE                                                      
       {"1":5.8977,"2":5.8977,"3":5.8977,"4":5.8977,"5":5.8977}   



---------------------------

Example 9-50 XML Output

Run the Select statement to get an XML output.

%script

SELECT * FROM table(rqIndexEval2(
        par_lst => '{"rseed":99, "ore_parallel_flag":true, "ore_service_level":"MEDIUM"}',
        out_fmt => 'XML',
        times_num => 5,
        scr_name => 'computeMean'));

The result is:

NAME   VALUE                                                                              
1      <root><vector_obj><ROW-vector_obj><value>5.897744</value></ROW-vector_obj></vect   
2      <root><vector_obj><ROW-vector_obj><value>5.897744</value></ROW-vector_obj></vect   
3      <root><vector_obj><ROW-vector_obj><value>5.897744</value></ROW-vector_obj></vect   
4      <root><vector_obj><ROW-vector_obj><value>5.897744</value></ROW-vector_obj></vect   
5      <root><vector_obj><ROW-vector_obj><value>5.897744</value></ROW-vector_obj></vect

Parent topic: Embedded R Execution Functions (Autonomous Database)