Skip to content

Getting Started

The current preview release of Oracle Database MLE allows Oracle Database users to write extensions in Python. Please note, however, that the current Python implementation is experimental and therefore less robust than JavaScript.

Hello World

We start with a simple example that deploys a Python function in Oracle Database MLE that returns a string.

The Oracle Database MLE requires all Python extensions to be implemented as Python packages. These packages need to be zipped and can be deployed via newly the integrated CREATE SOURCE statement.

For our helloworld example, we create a file hello.py with the following content:

def hello(s_in: str) -> str:
    return "Hello " + s_in + "!"

exports['hello'] = hello

The hello function takes one argument and returns a string that includes this argument. We need to add exports['hello'] = hello in order to export the function.

Python sources cannot be deployed using the AS clause of CREATE SOURCE. Instead, we have to pack the source files into a zip file, store this zip file as a BLOB and then run a CREATE SOURCE statement that uses this BLOB.

Packing the source file hello.py into a zip file can be done as follows:

zip hello.zip hello.py

The zipfile is then loaded into the BLOB column of a table my_python_source that is created via:

CREATE TABLE my_python_source (id NUMBER, text BLOB);

We use SQL*Loader to load the zip file with the following control file sourceloader_python.ctl:

LOAD DATA
INFILE *
  INTO TABLE my_python_source
  REPLACE
  FIELDS terminated by ','
   ( 
     id CHAR(1),
     lob_file FILLER char,
     text LOBFILE(lob_file) TERMINATED BY EOF
   )
BEGINDATA
4,hello.zip

We run SQL*Loader as follows to load the zipfile into the database:

sqlldr scott/tiger@ORCLPDB1 control=sourceloader_python.ctl

After the file is loaded into the table my_python_source, we deploy the zipfile using the following statement in SQL*Plus:

CREATE OR REPLACE PYTHON SOURCE NAMED "hello.py"
USING BLOB SELECT text FROM my_python_source WHERE id = 4;

We now need to create a call specification for the hello function. In order to do that we run the following statement:

CREATE OR REPLACE FUNCTION HELLO("s_in" IN VARCHAR2) RETURN VARCHAR2 
AS LANGUAGE PYTHON
NAME 'hello\.py.hello(s_in str) return str';
/

We now can execute the procedure in SQL*Plus via:

select hello('World') from dual;

The output of the statement is as follows:

HELLO('WORLD')
--------------------------------------------------------------------------------
Hello World!