rqIndexEval2 Function

10.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 10-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 10-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

The following code runs a SQL query to retrieve all columns from the result of an rqIndexEval2 function call, which generates a PNG output based on a specified script and environment.

select *
from table(rqIndexEval2(
par_lst => '{"ore_graphics_flag":true}',
out_fmt => 'PNG',
times_num => 2,
scr_name => 'test_ggplot2_idx',
scr_owner => NULL,
env_name => 'myrenv'));

The output appears as follows:

NAME ID      VALUE                 IMAGE
---------- ---------- -------------------- ------------------------------
TIME_1        1       "hello world"        89504E470D0A1A0A0000000D494844
                                           52000001E0000001E008060000007D
                                           D4BE950000200049444154789CEDDD    
                                           777454D5DEC6F167D2430A2DF4163A
                                           A1080ABC5C848480A2808A2055B080
                                           88800D29F6
TIME_2        1       "hello world"        89504E470D0A1A0A0000000D494844
                                           52000001E0000001E008060000007D
                                           D4BE950000200049444154789CEDDD
                                           799C4E75FFC7F1F735FB98856130F6
NAME        ID        VALUE                IMAGE
---------- ---------- -------------------- ------------------------------
B11B4B64F9B9C58CA194A544765224
D12A4BDA77

Parent topic: Embedded R Execution Functions (Autonomous Database)