Example 10-45 Using an rqGroupEval2 Function

This example uses the IRIS table created in the example shown in rqTableEval2 Function (Autonomous Database). Define the R function and store it with the name groupCount in the script repository.

%script
BEGIN
    sys.rqScriptCreate('groupCount',
        'function(dat){
            x <- data.frame(table(dat$Species))
            names(x) <- c("Species", "Count")
            x}',
        FALSE, TRUE); -- V_GLOBAL, V_OVERWRITE
END;
/

The output is similar to the following:

PL/SQL procedure successfully completed. 

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

Example 10-46 JSON Output

Calls the rqGroupEval2 function, which runs the user defined function groupCount. In the function, the INP_NAM argument specifies the data in the IRIS table to pass to the function. The PAR_LST argument specifies the special control argument ore_input_type. In the OUT_FMT argument, the string 'JSON', specifies that the table returned contains a CLOB that is a JSON string The GRP_COL parameter specifies the column to group by. The SCR_NAME parameter specifies the user-defined R function stored with the name groupCount in the script repository.

%script
set long 500
SELECT * FROM table(rqGroupEval2(
            inp_nam => 'IRIS',
            par_lst => '{"ore_service_level":"MEDIUM", "ore_parallel_flag":true}',
            out_fmt => 'JSON',
            grp_col => 'Species',
            scr_name => 'groupCount'));

The output is similar to the following:

NAME VALUE 
[{"Count":50,"Species":"setosa"},{"Count":50,"Species":"versicolor"},{"Count":50,"Species":"virginica"}]

Example 10-47 XML Output

Calls the rqGroupEval2 function, which runs the user defined function groupCount. In the function, the INP_NAM argument specifies the data in the IRIS table to pass to the function. The PAR_LST argument specifies using MEDIUM service level with special control argument ore_service_level and set the special control argument ore_parallel_flag to true. The OUT_FMT parameter specifies returning the value in XML format. The GRP_COL parameter specifies the column to group by. The SCR_NAME parameter specifies the user-defined R function stored with the name groupCount in the script repository.

%script
set long 500
SELECT * FROM table(rqGroupEval2(
            inp_nam => 'IRIS',
            par_lst => '{ore_service_level":"MEDIUM", "ore_parallel_flag":true}',
            out_fmt => 'XML',
            grp_col => 'Species',
            scr_name => 'groupCount'));

The ouput is similar to the following:

NAME VALUE
       <root><frame_obj><ROW-frame_obj><Species>setosa</Species><Count>50</Count></ROW-frame_obj><ROW-frame_obj><Species>versicolor</Species><Count>50</Count></ROW-frame_obj><ROW-frame_obj><Species>virginica</Species><Count>50</Count></ROW-frame_obj></frame_obj></root>

Example 10-48 Relational Output

Run the Select statement to get a Relational output.

%script
SELECT * FROM table(rqGroupEval2(
            inp_nam => 'IRIS',
            par_lst => '{"ore_service_level":"MEDIUM", "ore_parallel_flag":true}',
            out_fmt => '{"Species":"VARCHAR2(10)", "Count":"NUMBER"}',
            grp_col => 'Species',
            scr_name => 'groupCount'));

Species Count 
setosa 50 
versicolor 50 
virginica 50