ORA-24816 raised when Text (LOB) column is not at the end of SQLAlchemy model – inconsistent behavior

[Saqibs575]

Hi,

I'm encountering inconsistent behavior when inserting into a table using SQLAlchemy with an Oracle backend. Specifically, I'm getting the following error when inserting data with a Text (LOB) column not at the end of the model definition:

ORA-24816: Expanded non LONG bind data supplied after actual LONG or LOB column

The Problem

I have two different tables, both with a Text column (defined in SQLAlchemy as Column('request_data', Text)), and several other columns.

In one table, if the Text column is not defined last in the model (i.e., some other columns are declared after it), I get the ORA-24816 error during session.add() and session.commit().

However, in another table, the Text column is in the middle of the model, and even though it appears in the middle in the generated SQL (engine.echo = True), no error is raised — the insert works fine.

This inconsistency is puzzling. I would expect either:

• SQLAlchemy or the Oracle DB driver (oracledb) to handle bind ordering automatically to avoid ORA-24816

• Or the behavior to be consistent across tables

Workaround

If I reorder the columns in the SQLAlchemy model such that the Text column appears last, the error goes away.

But since this behavior is not consistent, it makes the fix brittle and confusing.

Models For Reference

class CoordinatorMaster(BaseModel):
    """
    Job status table
    """
    __tablename__ = 'coo_master'
    id = Column('id', Integer, primary_key=True, autoincrement=True)
    client_id = Column('client_id', Integer)
    mode_of_execution = Column('mode_of_execution', String(200))
    request_data = Column('request_data', Text)
    status = Column('status',  String(100), default="")
    coordinator_type = Column('coordinator_type',  String(100))
    service_id = Column('service_id', String(100))


class DataSources(BaseModel):
    """
    DataSources table
    """
    __tablename__ = 'das_datasource'
    id = Column('id', Integer, primary_key=True, autoincrement=True)
    client_id = Column('client_id', Integer)
    datasource_id = Column('datasource_id', String(100))
    datasource_info = Column('datasource', Text)
    is_deleted = Column('is_deleted', Boolean, default=False)
    created_date = Column('created_date', DateTime)
    created_by = Column('created_by', String(100))

    __table_args__ = (
        Index('edc_das_index', id.desc(), client_id, active, datasource_id,
              created_date.desc()),
    )

Encountering error for CoordinatorMaster (coo_master)

Details

• SQLAlchemy: 2.0.41
• oracledb: 3.0.0
• Python: 3.12.10
• OS: Windows 10/11

[Saqibs575]

Thanks for the guidance! zzzeek CaselIT

I was able to resolve the issue — it turns out the error was caused by one or more column values (non-CLOB) being None or empty strings and placed after the CLOB (Text) column in the model. Oracle's LOB handling seems sensitive to this bind order.

Reordering the columns so that the CLOB column comes last — or ensuring non-CLOB fields after it are properly populated — resolved the ORA-24816 error.

Appreciate the help and insights!