CODEWITHSUNDEEP
oracle-databasepython-oracledb
Kazuma Kusu
Kazuma Kusu

Reputation: 5

Error in Direct Binding of JSON Data with python-oracledb Library on Oracle Database 23ai

I tried to load JSON data using direct binding as described in this example. I encountered the error oracledb.exceptions.NotSupportedError: DPY-3002: Python value of type "dict" is not supported.

Here is the code I executed:

import os
import json
import random
import oracledb

assert "ORACLE_USER" in os.environ
assert "ORACLE_PASSWORD" in os.environ
assert "ORACLE_DSN" in os.environ
assert "ORACLE_MODE" in os.environ
assert "ORACLE_JSON_BENCH_TABLE" in os.environ

if __name__ == "__main__":
    username = os.environ["ORACLE_USER"]
    password = os.environ["ORACLE_PASSWORD"]
    dsn = os.environ["ORACLE_DSN"]
    table_name = os.environ["ORACLE_JSON_BENCH_TABLE"]
    is_thick = os.environ["ORACLE_MODE"] == "THICK"

    # use thick mode if available
    if is_thick:
        oracledb.init_oracle_client()

    conn = oracledb.connect(user=username, password=password, dsn=dsn)

    # collect db and client info
    client_version = 0
    if not conn.thin:
        client_version = oracledb.clientversion()[0]
    db_version = int(conn.version.split(".")[0])

    jsondict = dict(text="")
    with conn.cursor() as cur:
        query = f"INSERT INTO {table_name} VALUES (:1)"
        jsondict["text"] = jsondict["text"] + chr(random.randint(ord("A"), ord("z")))
        if conn.thin or client_version >= 21:
            # direct binding
            cur.setinputsizes(None, oracledb.DB_TYPE_JSON)
            cur.execute(query, [jsondict])
        else:
            cur.execute(query, [json.dumps(jsondict)])

    conn.close()

Upvotes: 0

Views: 284

Answers (1)

Christopher Jones
Christopher Jones

Reputation: 10691

To explain the solution noted in my comment, the syntax:

cur.setinputsizes(None, oracledb.DB_TYPE_JSON)

would be used for a SQL statement that contained two bind variable placeholders, such as

"insert into CustomersAsJson values (:1, :2)"

In that statement None means use the default mapping (e.g. when binding a Python number to a NUMBER column) for the first bind variable, and then the second bind variable is a JSON value.

Because your INSERT statement only had a single bind placeholder for your JSON column:

query = f"INSERT INTO {table_name} VALUES (:1)"

you needed to change the setinputsizes() call to be cur.setinputsizes(oracledb.DB_TYPE_JSON).

Upvotes: 1

Related Questions