Blog · Oracle

ORA-02293 when a Django TextField becomes a JSONField on Oracle

A JSONField on Oracle is an NCLOB column with a check constraint. Oracle validates that constraint against every existing row. One row of text that is not JSON stops the migration.

manage.py migrate
# django.db.utils.DatabaseError: ORA-02293: cannot validate
# (SCHEMA.TESTS_LEG_PAYLOAD_7B24DBFA_C) - check constraint violated

Read the column definition to see what Django built. The constraint is separate from the column, and its name is a truncated form.

SELECT column_name, data_type FROM user_tab_columns
 WHERE table_name = 'TESTS_DOC';
# DATA   NCLOB

SELECT constraint_name, search_condition FROM user_constraints
 WHERE table_name = 'TESTS_DOC' AND constraint_type = 'C';
# SYS_C008650   "DATA" IS JSON

Two error codes come from the one constraint. ORA-02293 arrives at migration time, because Oracle validates the old rows as it adds the constraint. ORA-02290 arrives later, on a write that puts text that is not JSON into a column that already holds the constraint.

SELECT id FROM tests_legacy WHERE payload IS NOT JSON;

Repair the rows, then migrate

The repair has to run before the type change. Two separate migrations are the safer shape. Oracle commits each DDL statement on its own. Thus a mixed migration that fails at the AlterField leaves the data repair committed and the migration unrecorded. Two files keep the repair re-runnable on its own.

# 0002_clean_payload.py
def clean(apps, schema_editor):
    Legacy = apps.get_model("myapp", "Legacy")
    for row in Legacy.objects.exclude(payload=None).iterator():
        try:
            json.loads(row.payload)
        except ValueError:
            Legacy.objects.filter(pk=row.pk).update(
                payload=json.dumps({"legacy_text": row.payload}))

class Migration(migrations.Migration):
    operations = [migrations.RunPython(clean, migrations.RunPython.noop)]

# 0003_payload_to_json.py -- a separate file
operations = [
    migrations.AlterField("legacy", "payload",
                          models.JSONField(null=True)),
]

The example above keeps the old text under a key. A project that treats the bad rows as waste sets the column to NULL instead. Decide which of the two applies before the migration reaches production data, because the second choice destroys the text.

Why the other databases accept it

PostgreSQL stores a JSONField as jsonb, and the type itself rejects a bad value. SQLite stores text and validates nothing at the database level. Neither one produces this error, so the migration passes in development and stops at Oracle.

The constraint is a benefit, not a defect. It states that every row in the column is valid JSON, and Oracle enforces that for writes from outside Django as well.