13.6.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_LST 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_FMT parameter.
Syntax
pyqEval(
par_lst VARCHAR2,
out_fmt VARCHAR2,
scr_name VARCHAR2,
scr_owner VARCHAR2 DEFAULT NULL)Parameters
| Parameter | Description |
|---|---|
|
|
A JSON string that contains additional parameters to pass to the
user-defined Python function specified by the For example, to specify the input data type as
|
|
|
The format of the output returned by the function. It can be one of the following:
|
|
|
The name of a user-defined Python function in the OML4Py script repository. |
|
|
The owner of the registered Python script. The default value is NULL. If NULL, will search for the Python script in the user’s script repository. |
Returns
Function pyqEval returns a table that has the structure
specified by the OUT_FMT parameter value.
Example 13-17 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;
/