13.7.5 Output Formats (Autonomous Database)
The OUT_FMT parameter controls the format of output returned by the table functions pyqEval, pyqGroupEval, pyqIndexEval, pyqRowEval, pyqTableEval, and pyqJobResult.
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 invokes the pyqEval function on the 'pyqFun1' created in the pyqEval function section.
SQL> select *
from table(pyqEval(
par_lst => '{"oml_service_level":"MEDIUM"}',
out_fmt => 'JSON',
scr_name => 'pyqFun1'));
NAME
----------------------------------------------------------------------
VALUE
----------------------------------------------------------------------
[{"FLOAT":0,"ID":0,"NAME":"demo_0"},{"FLOAT":0.1,"ID":1,"NAME":"demo_1
"},{"FLOAT":0.2,"ID":2,"NAME":"demo_2"},{"FLOAT":0.3,"ID":3,"NAME":"de
mo_3"},{"FLOAT":0.4,"ID":4,"NAME":"demo_4"},{"FLOAT":0.5,"ID":5,"NAME"
:"demo_5"},{"FLOAT":0.6,"ID":6,"NAME":"demo_6"},{"FLOAT":0.7,"ID":7,"N
AME":"demo_7"},{"FLOAT":0.8,"ID":8,"NAME":"demo_8"},{"FLOAT":0.9,"ID":
9,"NAME":"demo_9"}]
1 row selected.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(7)", "DIFF":"number"}, the output should contain a NAME column of type VARCHAR2(7) and a DIFF column of type NUMBER. The following example uses the table GRADE and the script 'computeGradeDiff' (created in Asynchronous Jobs (Autonomous Database) and invokes the computeGradeDiff function:
SQL> select *
from table(pyqTableEval(
inp_nam => 'GRADE',
par_lst => '{"oml_input_type":"pandas.DataFrame"}',
out_fmt => '{"NAME":"varchar2(7)","DIFF":"number"}',
scr_name => 'computeGradeDiff'));NAME DIFF
------- ----------
Abbott 3
Branfor -5
Crandel 10
Denniso 13
Edgar 9
Faust 5
Greeley -9
Hart 4
Isley 2
Jasper 8
10 rows selected.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 pyqEval, pyqTableEval,pyqRowEval function returns. For pyqGroupEval, pyqIndexEval, 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 Python objects first, followed by the image or images generated by the Python function. Images are returned as a base 64 encoding of the PNG representation. To include images in the XML string, the special control argument oml_graphics_flag must be set to true.
In the following code, the python function gen_two_images is defined and stored with name plotTwoImages in the script repository. The function renders two subplots with random dots in red and blue color and returns the number of columns of the input data.
begin
sys.pyqScriptCreate('plotTwoImages','def gen_two_images (dat):
import numpy as np
import matplotlib.pyplot as plt
np.random.seed(22)
fig = plt.figure(1);
fig2 = plt.figure(2);
ax = fig.add_subplot(111);
ax.set_title("Random red dots")
ax2 = fig2.add_subplot(111);
ax2.set_title("Random blue dots")
ax.plot(range(100), np.random.normal(size=100), marker = "o",
color = "red", markersize = 2)
ax2.plot(range(100,0,-1), marker = "o", color = "blue", markersize = 2)
return dat.shape[1]
',FALSE,TRUE);
end;
/The following example shows the XML output of a pyqRowEval function call where both structured data and images are included in the result:
SQL> select *
from table(pyqRowEval(
inp_nam => 'GRADE',
par_lst => '{"oml_graphics_flag":true}',
out_fmt => 'XML',
row_num => 5,
scr_name => 'plotTwoImages'
));NAME
--------------------------------------------------------------------------------
VALUE
----------------------------------------------------------------------
1
<root><Py-data><int>7</int></Py-data><images><image><img src="data:ima
ge/pngbase64"><![CDATA[iVBORw0KGgoAAAANSUhEUgAAAoAAAAHgCAYAAAA10dzkAAA
ABHNCSVQICAgIfAhkiAAAAAlwSFlzAAAPYQAAD2EBqD+naQAAADh0RVh0U29mdHdhcmUAb
WF0cGxvdGxpYiB2ZXJzaW9uMy4xLjIsIGh0dHA6Ly9tYXRwbG90bGliLm9yZy8li6FKAAA
gAElEQVR4nOydeZwcVb32n549k0xCSMhGEohhEZFNUAEBE0UUIYOACG4gFxWvgGzqldf3s
lz1xYuKLBe3i7LcNyhctoxsviCJoAQFNAKCCLITQyCQbZJMZqb
2
<root><Py-data><int>7</int></Py-data><images><image><img src="data:ima
ge/pngbase64"><![CDATA[iVBORw0KGgoAAAANSUhEUgAAAoAAAAHgCAYAAAA10dzkAAA
ABHNCSVQICAgIfAhkiAAAAAlwSFlzAAAPYQAAD2EBqD+naQAAADh0RVh0U29mdHdhcmUAb
WF0cGxvdGxpYiB2ZXJzaW9uMy4xLjIsIGh0dHA6Ly9tYXRwbG90bGliLm9yZy8li6FKAAA
gAElEQVR4nOydeZwcVb32n549k0xCSMhGEohhEZFNUAEBE0UUIYOACG4gFxWvgGzqldf3s
lz1xYuKLBe3i7LcNyhctoxsviCJoAQFNAKCCLITQyCQbZJMZqb
2 rows selectedPNG
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 oml_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 pyqEval and pyqTableEval function returns. For pyqRowEval, pyqGroupEval, pyqIndexEval, 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 TITLE column contains the titles of the rendered PNG images. 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 pyqRowEval function call.
SQL> column name format a7
column valueformat a5
column title format a16
column image format a15
select *
from table(pyqRowEval(
inp_nam => 'GRADE',
par_lst => '{"oml_graphics_flag":true}',
out_fmt => 'PNG',row_num => 5,
scr_name => 'plotTwoImages',
scr_owner =>NULL
));NAME ID VALUE TITLE IMAGE
-------- --------- ----- ---------------- ---------------
CHUNK_1 1 7 Random red dots 6956424F5277304
B47676F41414141
4E5355684555674
141416F41414141
486743415941414
1413130647A6B41
41414142484E435
356514943416749
6641686B6941414
141416C7753466C
7A41414150
CHUNK_1 2 7 Random blue dots 6956424F5277304
B47676F41414141
4E5355684555674
141416F41414141
486743415941414
1413130647A6B41
41414142484E435
356514943416749
6641686B6941414
141416C7753466C
7A41414150
CHUNK_2 1 7 Random red dots 6956424F5277304
B47676F41414141
4E5355684555674
141416F41414141
486743415941414
1413130647A6B41
41414142484E435
356514943416749
6641686B6941414
141416C7753466C
7A41414150
CHUNK_2 2 7 Random blue dots 6956424F5277304
B47676F41414141
4E5355684555674
141416F41414141
486743415941414
1413130647A6B41
41414142484E435
356514943416749
6641686B6941414
141416C7753466C
7A41414150
4 rows selected.Asynchronous Mode Output
When you set oml_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.
See also oml_async_flag Argument.
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 pyqJobResult call.
The following example shows how to get the JSON output from an asynchronous pyqIndexEval function call:
SQL> select *
from table(pyqGroupEval(
inp_nam => 'GRADE',
par_lst => '{"oml_async_flag":true, "oml_graphics_flag":true}',
out_fmt => NULL,
grp_col => 'GENDER',
ord_col => NULL,
scr_name => 'inp_twoimgs',
scr_owner => NULL
));NAME
--------------------------------------------------------------------
VALUE
--------------------------------------------------------------------
https://<host name>/oml/tenants/<tenant name>/databases/<database
name>/api/py-scripts/v1/jobs/<job id>
1 row selected.SQL> select * from pyqJobStatus(
job_id => '<job id>');NAME
--------------------------------------------------------------------
VALUE
--------------------------------------------------------------------
https://<host name>/oml/tenants/<tenant name>/databases/<database
name>/api/py-scripts/v1/jobs/<job id>/result
1 row selected.SQL> column name format a7
column value format a5
column title format a16
column image format a15
select * from pyqJobResult(
job_id => '<job id>',
out_fmt => 'PNG'
);NAME ID VALUE TITLE IMAGE
------- ---------- ----- ---------------- ---------------
GROUP_F 1 7 Random red dots 6956424F5277304
B47676F41414141
4E5355684555674
141416F41414141
486743415941414
1413130647A6B41
41414142484E435
356514943416749
6641686B6941414
141416C7753466C
7A4141415059514
141443245427144
2B6E61514141414
468305256683055
32396D644864686
36D554162574630
634778766447787
0596942325A584A
7A615739754D793
4784C6A49734947
GROUP_F 2 7 Random blue dots 6956424F5277304
B47676F41414141
4E5355684555674
141416F41414141
486743415941414
1413130647A6B41
41414142484E435
356514943416749
6641686B6941414
141416C7753466C
7A4141415059514
141443245427144
2B6E61514141414
468305256683055
32396D644864686
36D554162574630
634778766447787
0596942325A584A
7A615739754D793
4784C6A49734947
GROUP_M 1 7 Random red dots 6956424F5277304
B47676F41414141
4E5355684555674
141416F41414141
486743415941414
1413130647A6B41
41414142484E435
356514943416749
6641686B6941414
141416C7753466C
7A4141415059514
141443245427144
2B6E61514141414
468305256683855
32396D644864686
36D554162574630
634778766447787
0596942325A584A
7A615739754D793
4784C6A49734947
GROUP_M 2 7 Random blue dots 6956424F5277304
B47676F41414141
4E5355684555674
141416F41414141
486743415941414
1413130647A6B41
41414142484E435
356514943416749
6641686B6941414
141416C7753466C
7A4141415059514
141443245427144
2B6E61514141414
468305256683055
32396D644864686
36D554162574630
634778766447787
0596942325A584A
7A615739754D793
4784C6A49734947
4 rows selectedAsynchronous 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 pyqIndexEval function call.
SQL> select *
from table(pyqIndexEval(
par_lst => '{"oml_async_flag":true}',
out_fmt => 'XML',
times_num => 3,
scr_name => 'idx_ret_df',
scr_owner => NULL
));NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
https://<host name>/oml/tenants/<tenant name>/databases/<database
name>/api/py-scripts/v1/jobs/<job id>
1 row selected.SQL> select * from pyqJobStatus(
job_id => '<job id>'
); 2
NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
https://<host name>/oml/tenants/<tenant name>/databases/<database
name>/api/py-scripts/v1/jobs/<job id>/result
1 row selected.SQL> select * from pyqJobResult(
job_id => '<job id>',
out_fmt => 'XML'
); 2 3 4
NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
1
<root><pandas_dataFrame><ROW-
pandas_dataFrame><ID>1</ID><RES>a</RES></ROW-pandas
_dataFrame></pandas_dataFrame></root>
2
<root><pandas_dataFrame><ROW-
pandas_dataFrame><ID>2</ID><RES>b</RES></ROW-pandas
_dataFrame></pandas_dataFrame></ro
3
<root><pandas_dataFrame><ROW-
pandas_dataFrame><ID>3</ID><RES>c</RES></ROW-pandas
_dataFrame></pandas_dataFrame></root>
3 rows selected