10.5.2 pyqEval Function (On-Premises Database)

This topic describes the pyqEval function when used in an on-premises Oracle Database. The pyqEval function runs a user-defined Python function that explicitly retrieves data or for which external data is to be automatically loaded for the function.

You can pass arguments to the Python function with the PAR_QRY parameter.

The pyqEval function does not automatically receive any data from the database. The Python function generates the data that it uses or it explicitly retrieves it from a data source such as Oracle Database, other databases, or flat files.

The Python function can return a boolean, a dict, a float, an int, a list, a str, a tuple or a pandas.DataFrame object. You define the form of the returned value with the OUT_QRY parameter.

Syntax

pyqEval (
    PAR_QRY     VARCHAR2       IN
    OUT_QRY     VARCHAR2       IN
    EXP_NAM     VARCHAR2       IN) 

Parameters

Parameter	Description
PAR_QRY	A JSON string that contains additional parameters to pass to the user-defined Python function specified by the EXP_NAM parameter. Special control arguments, which start with oml_, are not passed to the function specified by EXP_NAM, but instead control what happens before or after the invocation of the function. For example, to specify the input data type as pandas.DataFrame, use: '{"oml_input_type":"pandas.DataFrame"}'
OUT_QRY	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 name of a table or view to use as a prototype. If using a table or view owned by another user, use the format <owner name>.<table/view name>. You must have read access to the specified table or view. 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 Python objects first, followed by the image or images generated by the Python function. The string 'PNG', which specifies that the table returned contains a BLOB that has the image or images generated by the Python function. Images are returned as a base 64 encoding of the PNG representation.
EXP_NAM	The name of a user-defined Python function in the OML4Py script repository.

Returns

Function pyqEval returns a table that has the structure specified by the OUT_QRY parameter value.

Example 10-15 Using the pyqEval Function

This example defines Python functions and stores them in the OML4Py script repository. It invokes the pyqEval function on the user-defined Python functions.

In a PL/SQL block, create an unnamed Python function that is stored in script repository with the name pyqFun1.

BEGIN
   sys.pyqScriptCreate('pyqFun1', 'func = lambda: "Hello World from a lambda!"',
                        FALSE, TRUE); -- V_GLOBAL, V_OVERWRITE
END;
/

Invoke the pyqEval function, which runs the user-defined Python function and returns the results as XML.

SELECT name, value 
  FROM table(pyqEval(
             NULL,
             'XML',
             'pyqFun1'));

The output is the following.

NAME  VALUE
----  --------------------------------------------------       
      <root><str>Hello World from a lambda!</str></root>

Drop the user-defined Python function.

BEGIN
  sys.pyqScriptDrop('pyqFun1');
END;
/ 

Define a Python function that returns a numpy.ndarray that is stored in script repository with the name pyqFun2.

BEGIN
  sys.pyqScriptCreate('pyqFun2',
    'def return_frame():
       import numpy as np
       import pickle
       z = np.array([y for y in zip([str(x)+"demo" for x in range(10)],
                      [float(x)/10 for x in range(10)],
                      [x for x in range(10)],
                      [bool(x%2) for x in range(10)],
                      [pickle.dumps(x) for x in range(10)],
                      ["test"+str(x**2) for x in range(10)])],
                    dtype=[("a", "U10"), ("b", "f8"), ("c", "i4"), 
                           ("d", "?"), ("e", "S20"), ("f", "O")])
       return z');
END;
/

Invoke the pyqEval function, which runs the pyqFun2 user-defined Python function.

SELECT * 
  FROM table(pyqEval(
               NULL,
               '{"A":"varchar2(10)", "B":"number", 
                 "C":"number", "D":"number",
                 "E":"raw(10)", "F": "varchar2(10)" }',
                'pyqFun2'));

The output is the following.

A                   B          C          D E                    F
---------- ---------- ---------- ---------- -------------------- ----------
0demo               0          0          0 80034B002E           test0
1demo        1.0E-001          1          1 80034B012E           test1
2demo        2.0E-001          2          0 80034B022E           test4
3demo        3.0E-001          3          1 80034B032E           test9
4demo        4.0E-001          4          0 80034B042E           test16
5demo        5.0E-001          5          1 80034B052E           test25
6demo        6.0E-001          6          0 80034B062E           test36
7demo        7.0E-001          7          1 80034B072E           test49
8demo        8.0E-001          8          0 80034B082E           test64
9demo        9.0E-001          9          1 80034B092E           test81

10 rows selected.

Drop the user-defined Python function.

BEGIN
  sys.pyqScriptDrop('pyqFun2');
END;
/