Blog · Oracle

ORA-40478: a JSON value too long for a Django query on Oracle

A JSONField on Oracle holds a document of any size. A query that reads one string key out of that document does not. The limit for the extracted value is 4000 characters, and the behaviour at each side of the limit is different.

class Doc(models.Model):
    data = models.JSONField(default=dict)

Doc.objects.create(data={"note": "x" * 5000})

Doc.objects.get(pk=1).data["note"]           # 5000 -- correct
Doc.objects.values_list("data__note", flat=True)[0]
# '' -- empty, and no error

The document is intact. A read of the whole column returns all 5000 characters on every attempt. Only a query that extracts the one key fails. Django compiles that key into a key transform, and the transform asks Oracle for a value of at most 4000 characters.

Length of the string	Read of the whole column	Read of one key	`filter(key=value)`
4000 or fewer	full value	full value	ORA-40478
4001 or more	full value	empty, no error	0 rows, no error

Above 4000 characters the database reports no fault, and the query returns the wrong answer. filter() matches no row, and the row is present. values_list() gives an empty string, and the text is present. A startswith on the first three characters also matches nothing. To find the cause, compare a read of the whole column with a read of the one key. A difference between the two proves the limit, not a fault in the data.

Ask Oracle for a CLOB

Oracle returns the full value when the extraction states a wider return type. JSON_VALUE accepts RETURNING CLOB, and Django reaches it through RawSQL.

from django.db.models import TextField
from django.db.models.expressions import RawSQL

Doc.objects.annotate(
    note=RawSQL("JSON_VALUE(data, '$.note' RETURNING CLOB)", [],
                output_field=TextField())
).values_list("note", flat=True)
# 5000 -- the full value

RETURNING VARCHAR2(32767) also returns the full value, up to that length. Both forms are Oracle syntax, so a project that runs on more than one database needs a branch on connection.vendor.

The better answer is a column

A long text value belongs in a column of its own. A document is the correct place for a short scalar that a query reads: a name, a status, a date, an amount. It is the wrong place for a note field, a description, a rendered template, or a base64 payload.

class Doc(models.Model):
    data = models.JSONField(default=dict)   # short scalars
    note = models.TextField(blank=True)     # the long text

The column costs one migration. It removes the limit, it permits an index, and it keeps the same query behaviour on every database.

The other four databases

PostgreSQL, MySQL, MariaDB and SQLite have no limit of this kind. A read of a 32767-character value through a key transform returns the whole string on PostgreSQL. Thus a test suite on any of the four passes, and the fault appears after the deploy to Oracle.

Measured on Oracle Database 23ai Free and PostgreSQL 17, with Django 6.1 and the oracledb driver in thin mode. The lengths 3999, 4000, 4001 and 5000 were each tested against eight query shapes. Oracle stores a Django JSONField as NCLOB, so the document itself has no practical size limit.