Re: [cx-oracle-users] genesis of ORA-24816

[Michael B. <mi...@zz...>]

Hi, a bump here, wondering how an ORA-24816 can be reproduced with cx_oracle as one of my users is reporting it, so that I can figure out how to work around this, thanks !



On Apr 17, 2012, at 3:50 PM, Michael Bayer wrote:

> I have a user reporting this error message with SQLAlchemy / cx_oracle (cx_oracle/OCI versions unknown as of yet).    The official description of this message is:
> 
> ORA-24816:
> Expanded non LONG bind data supplied after actual LONG or LOB column 
> Cause: A Bind value of length potentially > 4000 bytes follows binding for LOB or LONG.
> Action: Re-order the binds so that the LONG bind or LOB binds are all at the end of the bind list.
> 
> The statement in question is an UPDATE statement.  The CLOB field is the only parameter in the SET clause, and there is an additional bound parameter in the WHERE clause, but of course there is no option but for this parameter to appear later in the statement due to the syntax of UPDATE.
> 
> There is some confusion over whether "re-order the binds" refers to the SQL string, or just the order in which the bound parameters are passed to cursor.execute().    But Python dictionaries, as is passed for the parameter set to cursor.execute(), are not ordered, so it would seem to be that if this user is getting this issue, then by definition cx_oracle has to be doing the wrong thing, or does not have enough information to do the right thing.  
> 
> The user claims the issue occurs when using sqlalchemy .execute() but not when using cx_oracle cursor.execute(), however SQLalchemy of course uses cursor.execute() here in any case, which further points to the issue ultimately hinging on dictionary ordering, which is something that can change randomly depending on program structure.
> 
> Curious how cx_oracle handles this restriction of OCI, that CLOBs must occur last, and also if cursor.setinputsizes() has any impact.  We are currently not calling setinputsizes() for CLOB fields as we observed other problems there (I'd need to re-test to see what those issues were).
> 
> thanks for any pointers here.
> 
> 
> 
> 
> 
> ------------------------------------------------------------------------------
> Better than sec? Nothing is better than sec when it comes to
> monitoring Big Data applications. Try Boundary one-second 
> resolution app monitoring today. Free.
> http://p.sf.net/sfu/Boundary-dev2dev
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users