pyqEval Function (Autonomous Database)

The function pyqEval does not automatically load the data. Within the user-defined Python function, the user may explicitly access and/or retrieve data using the transparency layer or an ROracle database connection.

Syntax

FUNCTION PYQSYS.pyqEval(
    PAR_LST    VARCHAR2, 
    OUT_FMT    VARCHAR2,
    SCR_NAME   VARCHAR2,
    SCR_OWNER  VARCHAR2 DEFAULT NULL,
    ENV_NAME   VARCHAR2 DEFAULT NULL
    )
    RETURN SYS.AnyDataSet

Parameters

Parameter	Description
`PAR_LST`	A JSON string that contains additional parameters to pass to the user-defined Python function specified by the `SCR_NAME` parameter. Special control arguments, which start with `oml_`, are not passed to the function specified by `SCR_NAME`, 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"}'` See also: Special Control Arguments (Autonomous Database).
`OUT_FMT`	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 Python function must return a `pandas.DataFrame`, a `numpy.ndarray`, a `tuple`, or a list of `tuples`. The string `'JSON'`, which specifies that the table returned contains a CLOB that is a JSON string. 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. See also: Output Formats (Autonomous Database).
`SCR_NAME`	The name of a user-defined Python function in the OML4Py script repository.
`SCR_OWNER`	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.
`ENV_NAME`	The name of the conda environment that should be used when running the named user-defined Python function.

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

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

begin
    sys.pyqScriptCreate('pyqFun1',
        'def fun_tab():
                import pandas as pd
                names = ["demo_"+str(i) for i in range(10)]
                ids = [x for x in range(10)]
                floats = [float(x)/10 for x in range(10)]
                d = {''ID'': ids, ''NAME'': names, ''FLOAT'': floats}
                scores_table = pd.DataFrame(d)
                return scores_table
',FALSE,TRUE); -- V_GLOBAL, V_OVERWRITE 
end; 
/

Next, call the pyqEval function, which runs the user-defined Python function.

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 pyqFun1 function in the script repository as the Python function to call.

The JSON output is a CLOB. You can call set long [length] to get more output.

set long 500
select *
    from table(pyqEval(
        par_lst => '{"oml_service_level":"LOW"}',
        out_fmt => 'JSON',
        scr_name => 'pyqFun1'));

The output is the following.

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.

Issue another query that invokes the same pyqFun1 script. The OUT_FMT argument specifies a JSON string that contains the column names and data types of the structured table output.

select *
    from table(pyqEval(
        par_lst => '{"oml_service_level":"LOW"}',
        out_fmt => '{"ID":"number", "NAME":"VARCHAR2(8)", "FLOAT":"binary_double"}',
        scr_name => 'pyqFun1'));

The output is the following:

ID NAME FLOAT
   0 demo_0 0.0
   1 demo_1 0.1
   2 demo_2 0.2
   3 demo_3 0.3
   4 demo_4 0.4
   5 demo_5 0.5
   6 demo_6 0.6
   7 demo_7 0.7
   8 demo_8 0.8
   9 demo_9 0.9


10 rows selected.

Use the following code to create the "seaborn" environment based on Python version 3.10 and upload the environment to the object storage owned by the Pluggable Database (PDB).

Note:

Admin privilege is required to create and manage the Conda environments.

create -n seaborn python=3.10 seaborn
upload seaborn --overwrite --description 'Python package for seaborn' -t python 3.10 -t
      application OML4PY

The data visualization library 'seaborn' is installed in the environment.

Use the following code to create the script 'test_seaborn_noinp':

begin
  sys.pyqScriptCreate('test_seaborn_noinp',  
  'def fun_tab():
  import seaborn as sns
  import matplotlib.pyplot as plt
  import numpy as np  
  import pandas as pd    
  data = np.random.multivariate_normal([0, 0], [[5, 2], [2, 2]], size=2000)    
  data = pd.DataFrame(data, columns=["x", "y"])    
  sns.displot(data["x"])    
  plt.title("Dist plot")    
  plt.show()
  return "hello world" ',FALSE,TRUE); -- V_GLOBAL, V_OVERWRITE
end;
/

This example calls the pyqEval function, which runs the specified Python script.

The PAR_LST argument specifies capturing images rendered in the script with the special control argument oml_graphics_flag.

In the OUT_FMT arguments, the string 'PNG', specifies returning a table with BLOB containing the images generated by the Python function.

The SCR_NAME parameter specifies the 'test_seaborn_noinp' script in the script repository as the Python function to call.

The ENV_NAME parameter specifies 'seaborn', which is the Conda environment to run the Python function.

select *
  from table(pyqEval(
        par_lst => '{"oml_graphics_flag":true}',
        out_fmt => 'PNG',
        scr_name => 'test_seaborn_noinp',
        scr_owner => NULL,
        env_name => 'seaborn'
));

The output is the following.

NAME
--------------------------------------------------------------------------------
        ID
----------
VALUE
--------------------------------------------------------------------------------
TITLE
--------------------------------------------------------------------------------
IMAGE
--------------------------------------------------------------------------------

         1
"hello world"

NAME
--------------------------------------------------------------------------------
        ID
----------
VALUE
--------------------------------------------------------------------------------
TITLE
--------------------------------------------------------------------------------
IMAGE
--------------------------------------------------------------------------------
Lineplot
89504E470D0A1A0A0000000D4948445200000280000001E0080600000035D1DCE400000039744558
74536F667477617265004D6174706C6F746C69622076657273696F6E332E332E332C206874747073

NAME
--------------------------------------------------------------------------------
        ID
----------
VALUE
--------------------------------------------------------------------------------
TITLE
--------------------------------------------------------------------------------
IMAGE
--------------------------------------------------------------------------------
3A2F2F6D6174706C6F746C69622E6F72672FC897B79C000000097048597300000F6100000F6101A8
3FA7690000682C49444154789CEDDD797C5355FE3FFE579236E9BEB7E942DBB414286B0B2D94820A
4AC7023A82A2022E2C83B801A37674147F0A2EDFCF1415114719D119293AC280CC208EC8A050D984

NAME
--------------------------------------------------------------------------------
        ID
----------
VALUE
--------------------------------------------------------------------------------
TITLE
--------------------------------------------------------------------------------
IMAGE
--------------------------------------------------------------------------------

In a PL/SQL block, define the Python function create_iris_table and store in the script repository with the name create_iris_table, overwriting any existing user-defined Python function stored in the script repository with the same name.

The create_iris_table function imports and loads the iris data set, creates two pandas.DataFrame objects, and then returns the concatenation of those objects.

BEGIN
    sys.pyqScriptCreate('create_iris_table',
        'def create_iris_table():
            from sklearn.datasets import load_iris
            import pandas as pd
            iris = load_iris()
            x = pd.DataFrame(iris.data, columns = ["Sepal_Length",\
                "Sepal_Width", "Petal_Length", "Petal_Width"])
            y = pd.DataFrame(list(map(lambda x: {0:"setosa", 1: "versicolor",\
                2: "virginica"}[x], iris.target)),\
                columns = ["Species"])
            return pd.concat([y, x], axis=1)',
        FALSE, TRUE); -- V_GLOBAL, V_OVERWRITE
   END;
/
CREATE TABLE IRIS AS
(SELECT * FROM pyqEval(
    NULL,
    '{"Species":"VARCHAR2(10)","Sepal_Length":"number",
      "Sepal_Width":"number","Petal_Length":"number",
      "Petal_Width":"number"}',
    'create_iris_table'
));