Oracle AI Database Embedded Python Execution

Introduction

Occasionally, we need to process data inside the database using algorithms or tools that are not written for SQL or PL/SQL. For example, a few days ago, I had to include in a project some Linked Open Data (LOD) datasets received from different sources. The datasets contain graphs as RDF (Resource Description Framework) data in various standard notations, including N-Triples (.nt), Turtle (.ttl), N-Quads (.nq), JSON-LD (.json), and RDF/XML (.rdf). To compare and process triples from all these datasets, I need to convert them to a common format. This can’t be achieved using PL/SQL. There is a library in Python that can do this well and easily called RDFLib. But how do I run a Python library in my Oracle AI Database instance using PL/SQL? Oracle Machine Learning (OML) for Python (OML4Py) has a feature called Embedded Python Execution (EPE). You can invoke user-defined Python functions directly in an Oracle AI Database instance by using familiar SQL statements and PL/SQL blocks. How does it work?

Database Environment

Use your OCI tenancy (it can be a Free tier account) to provision an Autonomous AI Database (ADB) instance, an Object Storage bucket, let’s name it MYOMLUSER-bucket, and an Auth token in OCI Identity and Security > your OCI User. Download the sample geo-data-Q121810372.rdf RDF/XML format graph file that describes geospatial data, and upload it to the MYOMLUSER-bucket. This RDF graph is the sample input for the user-defined Python function.

Contents of geo-data-Q121810372.rdf file:

<?xml version="1.0"?>
<rdf:RDF xmlns:rdf="http://www.w3.org/1999/02/22-rdf-syntax-ns#" xmlns:xsd="http://www.w3.org/2001/XMLSchema#" xmlns:ontolex="http://www.w3.org/ns/lemon/ontolex#" xmlns:dct="http://purl.org/dc/terms/" xmlns:rdfs="http://www.w3.org/2000/01/rdf-schema#" xmlns:owl="http://www.w3.org/2002/07/owl#" xmlns:wikibase="http://wikiba.se/ontology#" xmlns:skos="http://www.w3.org/2004/02/skos/core#" xmlns:schema="http://schema.org/" xmlns:cc="http://creativecommons.org/ns#" xmlns:geo="http://www.opengis.net/ont/geosparql#" xmlns:prov="http://www.w3.org/ns/prov#" xmlns:wd="http://www.wikidata.org/entity/" xmlns:data="https://www.wikidata.org/wiki/Special:EntityData/" xmlns:wds="http://www.wikidata.org/entity/statement/" xmlns:wdref="http://www.wikidata.org/reference/" xmlns:wdv="http://www.wikidata.org/value/" xmlns:wdt="http://www.wikidata.org/prop/direct/" xmlns:wdtn="http://www.wikidata.org/prop/direct-normalized/" xmlns:p="http://www.wikidata.org/prop/" xmlns:ps="http://www.wikidata.org/prop/statement/" xmlns:psv="http://www.wikidata.org/prop/statement/value/" xmlns:psn="http://www.wikidata.org/prop/statement/value-normalized/" xmlns:pq="http://www.wikidata.org/prop/qualifier/" xmlns:pqv="http://www.wikidata.org/prop/qualifier/value/" xmlns:pqn="http://www.wikidata.org/prop/qualifier/value-normalized/" xmlns:pr="http://www.wikidata.org/prop/reference/" xmlns:prv="http://www.wikidata.org/prop/reference/value/" xmlns:prn="http://www.wikidata.org/prop/reference/value-normalized/" xmlns:wdno="http://www.wikidata.org/prop/novalue/">
	<rdf:Description rdf:about="https://www.wikidata.org/wiki/Special:EntityData/Q121810372">
		<rdf:type rdf:resource="http://schema.org/Dataset"/>
		<schema:about rdf:resource="http://www.wikidata.org/entity/Q121810372"/>
		<cc:license rdf:resource="http://creativecommons.org/publicdomain/zero/1.0/"/>
		<schema:softwareVersion>1.0.0</schema:softwareVersion>
		<schema:version rdf:datatype="http://www.w3.org/2001/XMLSchema#integer">2404385564</schema:version>
		<schema:dateModified rdf:datatype="http://www.w3.org/2001/XMLSchema#dateTime">2025-09-13T16:47:57Z</schema:dateModified>
	</rdf:Description>
	<rdf:Description rdf:about="http://www.wikidata.org/entity/Q121810372">
		<rdf:type rdf:resource="http://wikiba.se/ontology#Item"/>
	</rdf:Description>
	<rdf:Description rdf:about="https://uk.wikipedia.org/wiki/%D0%93%D0%B5%D0%BE%D0%BF%D1%80%D0%BE%D1%81%D1%82%D0%BE%D1%80%D0%BE%D0%B2%D1%96_%D0%B4%D0%B0%D0%BD%D1%96">
		<rdf:type rdf:resource="http://schema.org/Article"/>
		<schema:about rdf:resource="http://www.wikidata.org/entity/Q121810372"/>
		<schema:inLanguage>uk</schema:inLanguage>
		<schema:isPartOf rdf:resource="https://uk.wikipedia.org/"/>
	</rdf:Description>
	<rdf:Description rdf:about="https://uk.wikipedia.org/">
		<wikibase:wikiGroup>wikipedia</wikibase:wikiGroup>
	</rdf:Description>
	<rdf:Description rdf:about="http://www.wikidata.org/entity/Q121810372">
		<wdt:P155 rdf:resource="http://www.wikidata.org/entity/Q11692743"/>
		<wdt:P155 rdf:resource="http://www.wikidata.org/entity/Q350758"/>
		<wdt:P279 rdf:resource="http://www.wikidata.org/entity/Q11692743"/>
		<wdt:P138 rdf:resource="http://www.wikidata.org/entity/Q122074678"/>
		<wdt:P4953>gf2011026297</wdt:P4953>
		<wdtn:P4953 rdf:resource="http://id.loc.gov/authorities/genreForms/gf2011026297"/>
		<wdt:P1382 rdf:resource="http://www.wikidata.org/entity/Q11692743"/>
		<wdt:P1382 rdf:resource="http://www.wikidata.org/entity/Q350758"/>
		<wdt:P31 rdf:resource="http://www.wikidata.org/entity/Q2424752"/>
		<wdt:P973 rdf:resource="https://www.oracle.com/autonomous-database/what-is-geospatial-database/#fundamentals-of-geospatial-data"/>
		<wdt:P1535 rdf:resource="http://www.wikidata.org/entity/Q125294902"/>
		<wdt:P13591>concept/39444ef0-824d-4166-8b26-5d4c0cc60711</wdt:P13591>
		<rdfs:label xml:lang="en">geospatial data</rdfs:label>
		<skos:prefLabel xml:lang="en">geospatial data</skos:prefLabel>
		<schema:name xml:lang="en">geospatial data</schema:name>
		<rdfs:label xml:lang="fr">données géospatiales</rdfs:label>
		<skos:prefLabel xml:lang="fr">données géospatiales</skos:prefLabel>
		<schema:name xml:lang="fr">données géospatiales</schema:name>
		<rdfs:label xml:lang="es">datos geoespacial</rdfs:label>
		<skos:prefLabel xml:lang="es">datos geoespacial</skos:prefLabel>
		<schema:name xml:lang="es">datos geoespacial</schema:name>
		<schema:description xml:lang="en">data geospatially</schema:description>
		<skos:altLabel xml:lang="fr">les données géospatiales</skos:altLabel>
		<skos:altLabel xml:lang="en">geo-data</skos:altLabel>
		<skos:altLabel xml:lang="en">geodata</skos:altLabel>
	</rdf:Description>
</rdf:RDF>

The first step is to create a Machine Learning user, named MYOMLUSER, in your ADB instance. Use Database Actions > Database Users dialog to perform this task. Make sure you set a strong password, like My#5tr0ng_Pa55W0rd, allow UNLIMITED quota on tablespace DATA, and grant the roles: Graph, OML, and REST, GraphQL, MongoDB API, and Web access.

Next, use Database Actions > SQL to run this PL/SQL block of code as ADMIN user to allow the standard OML user MYOMLUSER to access Object Storage locations from within the ADB instance. This procedure appends an access control entry (ACE) with the specified privilege to the access control list (ACL) for the given host, and creates the ACL if it does not exist yet.

BEGIN
  DBMS_NETWORK_ACL_ADMIN.append_host_ace (
    host       => '*.oraclecloudapps.com',
    ace        => xs$ace_type(privilege_list => xs$name_list('http','connect','resolve'),
                              principal_name => 'MYOMLUSER',
                              principal_type => xs_acl.ptype_db));
END;
/

Sample output:

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.237

You have to do the same for EPE. As an ADMIN user, append an ACE to the ACL of the cloud host. The ACL controls access to the cloud host from the database, and the ACE specifies the connect privilege granted to the specified user name, your normal OML user MYOMLUSER. The output is similar to the previous one.

exec pyqAppendHostAce('MYOMLUSER','adb.<region>.oraclecloudapps.com');

Sample output:

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.152

Conda Environment

Use the Oracle Machine Learning user interface connected as the ADMIN user to create a Conda environment with the necessary Python libraries. Here are some simple Conda commands you can use, like the search command to check the availability of certain libraries.

%conda
search '*rdflib*'

You could use the install command to install new libraries in the base Conda environment.

%conda
conda install rdflib rdflib-with-all

However, it is recommended to create a Conda environment with a distinct set of libraries tailored to your project. If you run multiple projects on the same ADB instance, you should have various Conda environments, one for each project. This is a basic example of how to create one. Actually, this is the approach I chose for my project, so I created a new Conda environment called myenv with a certain version of Python and some required libraries. This task has to be performed as user ADMIN.

%conda
create -n myenv python=3.13 rdflib rdflib-with-all scikit-learn pandas

The user ADMIN has to upload the custom Conda environment to Object Storage for other users, like MYOMLUSER, to download and use these environments.

%conda
env-lcm upload myenv --description 'Conda environment with rdflib' -t application "OML4PY-RDF" --overwrite

Output:

Uploading conda environment myenv
Upload successful for conda environment myenv

Sign out from the ADMIN user connection. Sign in as a standard OML user MYOMLUSER, create a new Notebook, and download the Conda environment to test the myenv Conda environment.

%conda
download myenv

Output:

Downloading conda environment myenv
Download successful for conda environment myenv

In order to use the libraries in myenv custom Conda environment, this has to be activated.

%conda
conda activate myenv

Output:

Conda environment 'myenv' activated

List all environments and see which one is active using the env list command.

%conda
env list

Output:

# conda environments:
#
base                     /opt/conda
myenv                 *  /u01/.conda/envs/myenv

Optional: list all Python libraries installed in the active environment with the list command.

%conda
list

Convert Graphs

The second step is to create a stored user-defined Python function and use it to perform the syntax conversion between the graph formats used as linked data. This function can be executed directly from SQL using a REST authentication token. In the same OML Notebook, as OML user MYOMLUSER, run these paragraphs. It is a good practice to test your Python code in the custom Conda environment before creating the stored user-defined Python function inside the Oracle AI Database instance using PL/SQL. This function has two input arguments: file_name and to_type (output graph format). Files received in different formats (.nt, .ttl, .nq, .json, .rdf) are uploaded to the Object Storage bucket called MYOMLUSER-bucket. The output files are saved in the same bucket, and will have the same name with a different extension depending on the to_type parameter. Replace the values for the oci_region and namespace variables under the OCI Details. Replace also the values for the username and password variables under Credential Details with your Auth token details from OCI Identity and Security > your OCI User. The main logic behind this code is the rdflib.Graph.serialize() function that converts the graph data to the new format. For more information about the formats you can serialize data to with RDFLib, please read the documentation on Loading and saving RDF.

%python
import oml

def convert_ld(file_name, to_type):
    import pandas as pd
    import requests
    import os
    from requests.auth import HTTPBasicAuth
    from rdflib import Graph
    # OCI Details
    oci_region = "uk-london-1"
    namespace = "5amp1enam35pac3"
    bucket_name = "MYOMLUSER-bucket"
    object_url = f"https://swiftobjectstorage.{oci_region}.oraclecloud.com/v1/{namespace}/{bucket_name}/"
    # Credential Details
    username = "OracleIdentityCloudService/yourOCIuser"
    # Password is your user Auth token from OCI Identity and Security > your OCI User
    password = "(+a;do]NOT>USE;[th1s"
    auth_object = HTTPBasicAuth(username, password)
    # Set HTTP headers
    headers = { "Content-Type": "text/plain" }
    # Get source file
    filename, file_extension = os.path.splitext(file_name)
    from_type = file_extension[1:]
    # Set the correct format of the file to be parsed
    if (from_type == "rdf"):
        from_type = "xml"
    dest_object = filename + "." + to_type
    try:
        response = requests.get(object_url + file_name, auth=auth_object, headers=headers)
        block = response.text
        # Create a Graph
        g = Graph()
        # Parse the source file based on the source format
        g.parse( data=block, format=from_type )
        # Convert the block to the new format
        file_content = g.serialize(format=to_type)
        response = requests.put(object_url + dest_object, auth=auth_object, headers=headers, data=file_content)  
        response.raise_for_status()
        result = file_name + " converted successfully - Status Code: " + str(response.status_code)
    except requests.exceptions.RequestException as err:
        result = "File conversion failed. Error: " + str(err)
    return pd.DataFrame({result})

fconv = oml.do_eval(func=convert_ld, file_name = "geo-data-Q121810372.rdf", to_type = "ttl")
fconv

Sample output:

                                                   0
0 geo-data-Q121810372.rdf converted successfully...

Another good practice is to drop any existing PL/SQL user-defined Python function with the same name.

%script
begin
  sys.pyqScriptDrop('pyqLinkedData', TRUE);
end;

Output (or you may get an ORA-20000: script 'pyqLinkedData' does not exist error if the script doesn’t exist):

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.051

If the Python code works fine, use PL/SQL to write a PL/SQL user-defined Python function that can be executed directly in your Oracle AI Database instance. This block creates a function named pyqLinkedData in the OML4Py script repository.

%script
begin
    sys.pyqScriptCreate('pyqLinkedData',
        'def convert_ld(file_name, to_type):
            import pandas as pd
            import requests
            import os
            from requests.auth import HTTPBasicAuth
            from rdflib import Graph
            # OCI Details
            oci_region = "uk-london-1"
            namespace = "5amp1enam35pac3"
            bucket_name = "MYOMLUSER-bucket"
            object_url = f"https://swiftobjectstorage.{oci_region}.oraclecloud.com/v1/{namespace}/{bucket_name}/"
            # Credential Details
            username = "OracleIdentityCloudService/yourOCIuser"
            # Password is your user Auth token from OCI Identity and Security > your OCI User
            password = "(+a;do]NOT>USE;[th1s"
            auth_object = HTTPBasicAuth(username, password)
            # Set HTTP headers
            headers = { "Content-Type": "text/plain" }
            # Get source file
            filename, file_extension = os.path.splitext(file_name)
            from_type = file_extension[1:]
            # Set the correct format of the file to be parsed
            if (from_type == "rdf"):
                from_type = "xml"
            dest_object = filename + "." + to_type
            try:
                response = requests.get(object_url + file_name, auth=auth_object, headers=headers)
                block = response.text
                # Create a Graph
                g = Graph()
                # Parse the source file based on the source format
                g.parse( data=block, format=from_type )
                # Convert the block to the new format
                file_content = g.serialize(format=to_type)
                response = requests.put(object_url + dest_object, auth=auth_object, headers=headers, data=file_content)  
                response.raise_for_status()
                result = file_name + " converted successfully - Status Code: " + str(response.status_code)
            except requests.exceptions.RequestException as err:
                result = "File conversion failed. Error: " + str(err)
            return pd.DataFrame({result})',
        TRUE,  -- Make the user-defined Python function global.
        TRUE); -- Overwrite any global user-defined Python function with the same name.
end;

Output:

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.051

Obtaining the REST authentication token for REST APIs provided by OML requires the URL from Database Actions > Related Services > Oracle Machine Learning RESTful Services, having this format:

Get Valentin Leonard Tabacaru’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

https://<tenancy_ID>-<database_name>.adb.<region>.oraclecloudapps.com/omlusers/api/oauth2/v1/token

You could run a cURL command to get the token. For example, using the Cloud Shell, execute this command, replacing the arguments with your ADB instance values:

curl -X POST --header 'Content-Type: application/json' --header 'Accept: application/json' -d '{"grant_type":"password", "username":"MYOMLUSER", "password":"My#5tr0ng_Pa55W0rd"}' "https://8not81thisone3-d5example3sk7c03.adb.uk-london-1.oraclecloudapps.com/omlusers/api/oauth2/v1/token"

Sample output:

{"accessToken":"eyJhbGciNiJ9.eyJzdWIiOiJNWU9NTFVTR [...] sM2g","tokenType":"Bearer","expiresIn":3600}
username@cloudshell:~ (uk-london-1)$

However, you can get the token using a PL/SQL block. This way, you can request the token and set it as the access token for the EPE executions.

%script
declare
  l_Response clob;
  l_Token clob;
begin
  -- Populate the globals to send cookies and HTTP headers
  apex_web_service.g_request_headers(1).name := 'Content-Type';
  apex_web_service.g_request_headers(1).value := 'application/json';
  -- Invoke the RESTful style Web service
  l_Response := apex_web_service.make_rest_request(
        p_url => 'https://8not81thisone3-d5example3sk7c03.adb.uk-london-1.oraclecloudapps.com/omlusers/api/oauth2/v1/token',
        p_http_method => 'POST',
        p_body => '{"grant_type":"password", "username":"MYOMLUSER", "password":"My#5tr0ng_Pa55W0rd"}');
  dbms_output.put_line(l_Response);
  -- Extract the token string from the JSON response
  l_Token := JSON_VALUE(l_Response, '$.accessToken');
  dbms_output.put_line('------------------------------');
  dbms_output.put_line(l_Token);
  -- Once you have the authentication token, use it to set the access token in the token store
  pyqSetAuthToken(l_Token);
end;

Sample output:

{"accessToken":"eyJhbGciNiJ9.eyJzdWIiOiJNWU9NTFVTR [...] sM2g","tokenType":"Bearer","expiresIn":3600}
------------------------------
eyJhbGciNiJ9.eyJzdWIiOiJNWU9NTFVTR [...] sM2g

PL/SQL procedure successfully completed.

Once the authentication token is set, execute the user-defined Python function to perform the conversion. In this particular example, you will convert the sample RDF document from RDF/XML to Turtle format.

%sql
select JSON_VALUE(t.VALUE, '$.*') Value
    from table(pyqEval(
        par_lst => '{"oml_service_level":"HIGH", "file_name":"geo-data-Q121810372.rdf", "to_type":"ttl"}',
        out_fmt => 'JSON',
        scr_name => 'pyqLinkedData',
        env_name => 'myenv')) t;

This should be the output returned by the SQL query:

VALUE                                                            
---------------------------------------------------------------- 
geo-data-Q121810372.rdf converted successfully - Status Code: 201

Open your OCI Cloud Console and list the objects (files) in the MYOMLUSER-bucket Object Storage bucket. You should have a file named geo-data-Q121810372.ttl as the result of the execution. Download the file and open it in a text editor.

Contents of geo-data-Q121810372.ttl file:

@prefix cc: <http://creativecommons.org/ns#> .
@prefix data: <https://www.wikidata.org/wiki/Special:EntityData/> .
@prefix rdfs: <http://www.w3.org/2000/01/rdf-schema#> .
@prefix schema1: <http://schema.org/> .
@prefix skos: <http://www.w3.org/2004/02/skos/core#> .
@prefix wd: <http://www.wikidata.org/entity/> .
@prefix wdt: <http://www.wikidata.org/prop/direct/> .
@prefix wdtn: <http://www.wikidata.org/prop/direct-normalized/> .
@prefix wikibase: <http://wikiba.se/ontology#> .
@prefix xsd: <http://www.w3.org/2001/XMLSchema#> .

<https://uk.wikipedia.org/wiki/%D0%93%D0%B5%D0%BE%D0%BF%D1%80%D0%BE%D1%81%D1%82%D0%BE%D1%80%D0%BE%D0%B2%D1%96_%D0%B4%D0%B0%D0%BD%D1%96> a schema1:Article ;
    schema1:about wd:Q121810372 ;
    schema1:inLanguage "uk" ;
    schema1:isPartOf <https://uk.wikipedia.org/> .

data:Q121810372 a schema1:Dataset ;
    cc:license <http://creativecommons.org/publicdomain/zero/1.0/> ;
    schema1:about wd:Q121810372 ;
    schema1:dateModified "2025-09-13T16:47:57+00:00"^^xsd:dateTime ;
    schema1:softwareVersion "1.0.0" ;
    schema1:version 2404385564 .

<https://uk.wikipedia.org/> wikibase:wikiGroup "wikipedia" .

wd:Q121810372 a wikibase:Item ;
    rdfs:label "geospatial data"@en,
        "datos geoespacial"@es,
        "données géospatiales"@fr ;
    schema1:description "data geospatially"@en ;
    schema1:name "geospatial data"@en,
        "datos geoespacial"@es,
        "données géospatiales"@fr ;
    skos:altLabel "geo-data"@en,
        "geodata"@en,
        "les données géospatiales"@fr ;
    skos:prefLabel "geospatial data"@en,
        "datos geoespacial"@es,
        "données géospatiales"@fr ;
    wdtn:P4953 <http://id.loc.gov/authorities/genreForms/gf2011026297> ;
    wdt:P13591 "concept/39444ef0-824d-4166-8b26-5d4c0cc60711" ;
    wdt:P138 wd:Q122074678 ;
    wdt:P1382 wd:Q11692743,
        wd:Q350758 ;
    wdt:P1535 wd:Q125294902 ;
    wdt:P155 wd:Q11692743,
        wd:Q350758 ;
    wdt:P279 wd:Q11692743 ;
    wdt:P31 wd:Q2424752 ;
    wdt:P4953 "gf2011026297" ;
    wdt:P973 <https://www.oracle.com/autonomous-database/what-is-geospatial-database/#fundamentals-of-geospatial-data> .

Conclusions

Even though some tasks may seem impossible to execute from within the database, there are always solutions available, offered by Oracle AI Database, especially the Autonomous AI Database. OML4Py Embedded Python Execution is a powerful feature that gives unlimited possibilities to all database users to bring the logic layer closer to the data. Instead of moving the data to the processing tools, you can build specialized transformations and execute them on individual selected records. This example is meant to be used for learning purposes only, the scenario explained does not include compliance and security considerations required for a production environment. There are many aspects that have been omitted for the sake of simplicity. This approach is based on my personal experience, please feel free to leave a comment with your objections and recommendations.