does oracledb support the JSON "null" value

[zzzeek]

hey there -

JSON supports a string "null" that typically is what the Python json.dumps() encoder produces:

>>> import json
>>> json.dumps(None)
'null'

over at the table at https://python-oracledb.readthedocs.io/en/latest/user_guide/json_data_type.html#in-bind-type-mapping , it's a little bit ambiguous if oracledb's DB_TYPE_JSON encoder treats None as SQL NULL or as json "null". However, a test script as seen below indicates it treats None as SQL NULL. This is fine but as SQLAlchemy supports two different "none as NULL" modes for JSON, I think we're going to have to stick to using our own JSON serializer (and probably just add Decimal support to mirror oracledb).

"""Test that Python None round-trips as JSON null (not SQL NULL)
through oracledb's native JSON support with a NOT NULL column."""

import oracledb

# adjust connection params as needed
conn = oracledb.connect(
    user="scott", password="tiger", dsn="oracle23c/freepdb1"
)
cursor = conn.cursor()

table_name = "test_json_null"

# cleanup
cursor.execute(
    f"""
    BEGIN
        EXECUTE IMMEDIATE 'DROP TABLE {table_name}';
    EXCEPTION
        WHEN OTHERS THEN
            IF SQLCODE != -942 THEN RAISE; END IF;
    END;
    """
)

# create table with NOT NULL JSON column
cursor.execute(
    f"""
    CREATE TABLE {table_name} (
        id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
        data JSON NOT NULL
    )
    """
)

# insert Python None - oracledb should store this as JSON "null",
# not as SQL NULL (which would violate the NOT NULL constraint)
cursor.execute(
    f"INSERT INTO {table_name} (data) VALUES (:1)",
    [None],
)
conn.commit()

# read it back
cursor.execute(f"SELECT data FROM {table_name}")
row = cursor.fetchone()
result = row[0]

print(f"Result value: {result!r}")
print(f"Result type:  {type(result)}")

# verify it round-tripped as None (JSON null), not as something else
assert result is None, f"Expected None (JSON null), got {result!r}"

# verify the column is indeed NOT NULL - inserting actual SQL NULL
# should fail. Use a raw string to bypass oracledb's JSON conversion.
try:
    cursor.execute(
        f"INSERT INTO {table_name} (data) VALUES (NULL)"
    )
    print("ERROR: SQL NULL insert should have failed (NOT NULL constraint)")
except oracledb.IntegrityError as e:
    print(f"Correctly rejected SQL NULL: {e}")

cursor.execute(
    f"""
    BEGIN
        EXECUTE IMMEDIATE 'DROP TABLE {table_name}';
    END;
    """
)
conn.close()

print("\nAll checks passed.")

[anthony-tuininga]

The problem you are experiencing is that there are two legitimate possibilites: binding a NULL value and binding a not NULL value that is a JSON representation of NULL. The driver cannot know which one is intended! There is a method available (encode_oson) that you can use to enforce the encoding of the None value as OSON (Oracle's binary JSON implementation) and you can bind the encoded bytes directly. Maybe that will help? If SQLAlchemy knows that the NULL value supplied is supposed to be a JSON value, it could use this method to inform the driver accordingly.

[zzzeek]

ah maybe we could use the encode_oson thing. For now we just merged using our own serializer (but definitely oracle's deserializer on the way out, as we hit some big size limits trying to roll that ourselves).