1. User's Guide
  2. Embedded R Execution
  3. SQL API for Embedded R Execution with Autonomous Database
  4. Output Formats

9.6.5 Output Formats

The OUT_FMT parameter controls the format of output returned by the table functions rqEval2, rqGroupEval2, rqIndexEval2, rqRowEval2, rqTableEval2, and rqJobResult.

The output formats are:

JSON

When OUT_FMT is set to JSON, the table functions return a table containing a CLOB that is a JSON string.

The following example calls the rqEval2 function on the 'RandomRedDots2' created in the rqEval2 function section. 

%script

set long 500

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

---------------------------
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}]   



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

Relational

When OUT_FMT is specified with a JSON string where column names are mapped to column types, the table functions return the response by reshaping it into table columns. For example, if OUT_FMT is specified with {"NAME":"varchar2(10)", "COUNT":"number"}, the output should contain a NAME column of type VARCHAR2(10) and a COUNT column of type NUMBER. The following example uses the table rqGroupEval2 and the script groupCount (created in rqGroupEval2 Function and calls the groupCount function: 

%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

XML

When OUT_FMT is specified with XML, the table functions return the response in a table with fixed columns. The output consists of two columns. The NAME column contains the name of the row. The NAME column value is NULL for rqEval2, rqTableEval2,rqRowEval2 function returns. For rqGroupEval2, rqIndexEval2, the NAME column value is the group/index name. The VALUE column contains the 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. Images are returned as a base 64 encoding of the PNG representation. To include images in the XML string, the special control argument ore_graphics_flag must be set to true.

In the following code, the R function RandomRedDots2 is created in the script repository. 

%script

set long 500

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

The following example shows the XML output of a rqEval2 function call where both structured data and images are included in the result: 

set long 1000

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

--------------------------------------------------------------------------------
 NAME VALUE
----------------------------------------------------------------------

<root><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[iVBORw0KGgoAAAANSUhEUgAAAeAAAAHgCAYAAAB91L6VAAAgAElEQVR4nOzdd3hT5fvH8Xe6MzoopZS9ZE9BliiIgLKHooCgyBRRQFSGG0GUJSKKogxl42KIqCB8AQEVkD0rlL1HgTZJZ3r//gD9IXaEtkk67td15VJ6npzzOWmaO+ec5zyPQUQEpZRSSrmVl6cDKKWUUvmRFmCllFLKA7QAK6WUUh6gBVgppZTyAC3ASimllAdoAVZKKaU8QAuwUkop5QFagJVSSikP0AKslFJKeYAWYKWUUsoDtAArpZRSHqAFWCmllPIALcBKKaWUB2gBVkoppTxAC7BSSi

PNG

When OUT_FMT is specified with PNG, the table functions return the response in a table with fixed columns (including an image bytes column). When calling the SQL API, you must set the special control argument ore_graphics_flag to true so that the web server can capture images rendered in the executed script.

The PNG output consists of four columns. The NAME column contains the name of the row. The NAME column value is NULL for rqEval2 and rqTableEval2 function returns. For rqRowEval2, rqGroupEval2, rqIndexEval2, the NAME column value is the chunk/group/index name. The ID column indicates the ID of the image. The VALUE column contains the return value of the executed script. The IMAGE column is a BLOB column containing the bytes of the PNG images rendered by the executed script.

The following example shows the PNG output of a rqTableEval2 function call. 

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'));

---------------------------
NAME   ID   VALUE   IMAGE   
          1
      89504E470D0A1A0A0000000D49484452000001E0000001E008060000007DD4BE950000200049444154789CE

Note:

Here, only a portion of the output is shown. To determine the length of the output use the parameter set long [length].

Asynchronous Mode Output

When you set ore_async_flag to true to run an asynchronous job, set OUT_FMT to NULL for jobs that return non-XML results, or set it to XML for jobs that return XML results, as described below.

Asynchronous Mode: Non-XML Output

When submitting asynchronous jobs, for JSON, PNG, and relational outputs, set OUT_FMT to NULL when submitting the job. When fetching the job result, specify OUT_FMT in the rqJobResult call.

The following example shows how to get the JSON output from an asynchronous rqEval2 function call: 

%script

set long 500

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

https://<host name>/oml/api/r-scripts/v1/jobs/<job id>

1 row selected.
SQL> select * from rqJobStatus(
        job_id => '<job id>');
NAME
--------------------------------------------------------------------
VALUE
--------------------------------------------------------------------

https://<host name>/oml/api/r-scripts/v1/jobs/<job id>/result

1 row selected.
SQL> select * from rqJobResult(
     job_id => '<job id>',
     out_fmt => 'PNG'
     );
---------------------------
NAME   ID   VALUE   IMAGE   
          1
      89504E470D0A1A0A0000000D49484452000001E0000001E008060000007DD4BE95000020004944

Note:

Here, only a portion of the output is shown. To determine the length of the output use the parameter set long [length]

Asynchronous Mode: XML Output

If XML output is expected from the asynchronous job, you must set OUT_FMT to XML when submitting the job and fetching the job result.

The following example shows how to get the XML output from an asynchronous rqEval2 function call. 

set long 1000

SELECT * FROM table(rqEval2(
    par_lst => '{"ore_async_flag":true, "ore_graphics_flag":true, "ore_service_level":"LOW"}',
    out_fmt => 'XML',
    scr_name => 'RandomRedDots2'));
NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
https://<host name>/oml/api/r-scripts/v1/jobs/<job id>

1 row selected.
set long 500

SELECT * FROM rqJobStatus(
    job_id => '<Job id>'
);
  2  
NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
https://<host name>/oml/api/r-scripts/v1/jobs/<job id>/result

1 row selected.

set long 1000

SELECT * FROM rqJobResult(
    job_id => '<job id>',
    out_fmt => 'XML'
);

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[iVBORw0KGgoAAAANSUhEUgAAAeAAAAHgCAYAAAB91L6VAAAgAElEQVR4nOzdd3xTZfvH8U/StEmTtGUV2rKnCCjrARGZZcsegqgoKoKCqIAyHIgKylABUUFAZIuAAjIUZMkqZVVk7yl7tSTpSNP79wfqT7HjFJqctlzv16uvx6e5c873pKVXcs59rtuglFIIIYQQwqeMegcQQggh7kVSgIUQQggdSAEWQgghdCAFWAghhNCBFGAhhBBCB1KAhRBCCB1IARZCCCF0IAVYCCGE0IEUYCGEEEIHUoCFEEIIHUgBFkIIIXQgBVgIIYTQgRRgIYQQQgdSgIUQQggdSA

Parent topic: SQL API for Embedded R Execution with Autonomous Database