Example 9-31 Using rqEval2

This example defines a R function and stores it in the OML4R script respository. The PL/SQL block, creates the script RandomRedDots2 and add it to the script repository. Specify that the script is private and overwrite the script with the same name. It calls the rqEval2 function on the user defined R function.

BEGIN
  sys.rqScriptCreate('RandomRedDots2',
    '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;
/

Example 9-32 JSON Output

The PAR_LST argument specifies using LOW service level with the special control argument oml_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 RandomRedDots2 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(rqEval2(
    par_lst => '{"ore_service_level":"LOW"}',
    out_fmt => 'JSON',
    scr_name => 'RandomRedDots2'));

The result is:

NAME VALUE 
[{"val":0.01,"id":1},{"val":0.02,"id":2},{"val":0.03,"id":3},{"val":0.04,"id":4},{"val":0.05,"id":5},{"val":0.06,"id":6},{"val":0.07,"id":7},{"val":0.08,"id":8},{"val":0.09,"id":9},{"val":0.1,"id":10}]

Example 9-33 PNG Output.

The PAR_LST argument specifies using LOW service level with the special control argument oml_service_level. In the OUT_FMT argument, the string 'PNG' to include images returned by reqEval2. The SCR_NAME parameter specifies the RandomRedDots2 function in the script repository as the R function to call. The JSON output is a CLOB.

%script
SELECT * FROM table(rqEval2(
    par_lst => '
    {"ore_graphics_flag":true, "ore_service_level":"LOW"}',
    out_fmt => 'PNG',
    scr_name => 'RandomRedDots2'));

The result is:

---------------------------
NAME   ID   VALUE   IMAGE   
       1            89504E470D0A1A0A0000000D49484452000001E0000001E008060000007DD4BE950000200049444154789C

Example 9-34 XML Output.

The PAR_LST argument specifies using LOW service level with the special control argument oml_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 RandomRedDots2 function in the script repository as the R function to call. The JSON output is a CLOB.

%script
set long 1000
SELECT * FROM table(rqEval2(
    par_lst => '{"ore_service_level":"LOW"}',
    out_fmt => 'XML',
    scr_name => 'RandomRedDots2'));

The result is:

NAME VALUE
      <root><frame_obj><ROW-frame_obj><id>1</id><val>0.01</val></ROW-frame_obj><ROW-frame_obj><id>2</id><val>0.02</val></ROW-frame_obj><ROW-frame_obj><id>3</id><val>0.03</val></ROW-frame_obj><ROW-frame_obj><id>4</id><val>0.04</val></ROW-frame_obj><ROW-frame_obj><id>5</id><val>0.05</val></ROW-frame_obj><ROW-frame_obj><id>6</id><val>0.06</val></ROW-frame_obj><ROW-frame_obj><id>7</id><val>0.07</val></ROW-frame_obj><ROW-frame_obj><id>8</id><val>0.08</val></ROW-frame_obj><ROW-frame_obj><id>9</id><val>0.09<

Example 9-35 XML Output

Run the Select statement to get an XML output. ore_graphics_flag is set to true so that both structured data and images are included in the XML

%script


set long 1000

SELECT * FROM table(rqEval2(
    par_lst => '{"ore_graphics_flag":true, "ore_service_level":"LOW"}',
    out_fmt => 'XML',
    scr_name => 'RandomRedDots2'));

The result is:

---------------------------
NAME   VALUE                
<root><R-data><frame_obj><ROW-frame_obj><id>1</id><val>0.01</val></ROW-frame_obj><ROW-frame_obj><id>2</id><val>0.02</val></ROW-frame_obj><ROW-frame_obj><id>3</id><val>0.03</val></ROW-frame_obj><ROW-frame_obj><id>4</id><val>0.04</val></ROW-frame_obj><ROW-frame_obj><id>5</id><val>0.05</val></ROW-frame_obj><ROW-frame_obj><id>6</id><val>0.06</val></ROW-frame_obj><ROW-frame_obj><id>7</id><val>0.07</val></ROW-frame_obj><ROW-frame_obj><id>8</id><val>0.08</val></ROW-frame_obj><ROW-frame_obj><id>9</id><val>0.09</val></ROW-frame_obj><ROW-frame_obj><id>10</id><val>0.1</val></ROW-frame_obj></frame_obj></R-data><images><image><img src="data:image/pngbase64"><![CDATA[iVBORw0KGgoAAAANSUhEUgAAAeAAAAHgCAYAAAB91L6VAAAgAElEQVR4nOzdd3hT5fvH8Xe6m6QDSgtl07KRjciQIZvKkiECCspQBNkiICBLUBRxACoqU0RAkT0EREGGTCm77LJll2Z0pc/vD9Qf8u04QJPTcb+uq9clzZM8n9Qkd845zzAopRRCCCGEcCk3vQMIIYQQOZEUYCGEEEIHUoCFEEIIHUgBFkIIIXQgBVgIIYTQgRRgIYQQQgdSgIUQQggdSAEWQgghdCAFWAghhNCBFGAhhBBCB1KAhRBCCB1IARZCCCF0IAVYCCGE0IEUYCGEEEIHUoCFEEIIHU 

Example 9-36 Relational Output

Run the Select statement to get a Relational output. The OUT_FMT argument specifies a JSON string that contains the column names and data types of the table returned by rqEval2.

%script
SELECT * FROM table(rqEval2(
    par_lst => '{"ore_service_level":"LOW"}',
    out_fmt => '{"val":"NUMBER","id":"NUMBER"}',
    scr_name => 'RandomRedDots2'));

The result is:

val id 
0.01 1 
0.02 2 
0.03 3 
0.04 4 
0.05 5 
0.06 6 
0.07 7 
0.08 8 
0.09 9 
0.1 10 

10 rows selected.

Example 9-37 Passing arguments using rqEval2:

Run the Select statement to get an XML output by passing arguments to the rqEval2 function.

%script
set long 500
SELECT * FROM table(rqEval2(
        par_lst => '{"ore_service_level":"LOW", "divisor":50, "numDots":500}',
        out_fmt => 'XML',
        scr_name => 'RandomRedDots2'));

The result is:

NAME VALUE
       <root><frame_obj><ROW-frame_obj><id>1</id><val>0.01</val></ROW-frame_obj><ROW-frame_obj><id>2</id><val>0.02</val></ROW-frame_obj><ROW-frame_obj><id>3</id><val>0.03</val></ROW-frame_obj><ROW-frame_obj><id>4</id><val>0.04</val></ROW-frame_obj><ROW-frame_obj><id>5</id><val>0.05</val></ROW-frame_obj><ROW-frame_obj><id>6</id><val>0.06</val></ROW-frame_obj><ROW-frame_obj><id>7</id><val>0.07</val></ROW-frame_obj><ROW-frame_obj><id>8</id><val>0.08</val></ROW-frame_obj><ROW-frame_obj><id>9</id><val>0.09<