ORA-24816 error - Incorrect bind parameter order for CLOB update using Oracle dialect?

[Former user]

(original reporter: bmacauley) When attempting to insert a CLOB into an Oracle 11g database I get the following error...

sqlalchemy.exc.DatabaseError: (DatabaseError) ORA-24816: Expanded non LONG bind data supplied after actual LONG or LOB column

'UPDATE "EQDB_QUOTEBOM2" SET quote_bom_cpq_xml=:quote_bom_cpq_xml WHERE "EQDB_QUOTEBOM2".id = :EQDB_QUOTEBOM2_id' {'EQDB_QUOTEBOM2_id': '19600052-e2d2-41ba-950b-0fbde8d07567', 'quote_bom_cpq_xml': 'PD94bWwgdmVyc2lvbj0nMS4wJyBlbmNvZGluZz0nVVRGLTgnPz4KPHF1b3RlX2JvbV9jcHE+CiAgPG1lc3NhZ2VfaGVhZGVyPgogICAgPHNlbmRlcj5FUURCPC9zZW5kZXI+CiAgICA8cmVjZWl2ZXI+Q1BRPC9yZWNlaXZlcj4KICAgIDxtZXNzYWdlX2FjdGlvbj5jcmVhdGU8L21lc3NhZ2VfYWN0aW9uPgogICAgPG1lc3NhZ2VfaWQ+Yjc3MDU3MTMtYmI0MS00...

14k base64 CLOB

...jdXN0YWRkcXR5PjA8L2N1c3RhZGRxdHk+CiAgICAgIDwvbGluZV9pdGVtPgogICAgICA8bGluZV9pdGVtPgogICAgICAgIDxvcmRlcmNvZGU+OTUwLTAwMDQ3LTAxPC9vcmRlcmNvZGU+CiAgICAgICAgPHByb2R1Y3Q+Q1MyMDAwPC9wcm9kdWN0PgogICAgICAgIDxlbGVtZW50PjwvZWxlbWVudD4KICAgICAgICA8Z3JvdXBpbmc+PC9ncm91cGluZz4KICAgICAgICA8c3BlY19udW1iZXI+PC9zcGVjX251bWJlcj4KICAgICAgICA8Z2JhZGRxdHk+MTY8L2diYWRkcXR5PgogICAgICAgIDxjdXN0YWRkcXR5PjA8L2N1c3RhZGRxdHk+CiAgICAgIDwvbGluZV9pdGVtPgogICAgPC9saW5lX2l0ZW1zPgogIDwvZGV0YWlsPgo8L3F1b3RlX2JvbV9jcHE+Cg=='}

Environment:

Oracle 11g Windows 7 Activestate Python 2.7.1 cx_oracle 5.1.1 sqlalchemy 0.7.5

When inserting LOB's in Oracle the CLOB bind parameter must be ordered last. The SqlAlchemy generated sql suggests that the bind parameter ordering is correct. On further investigation, using a trace file on the oracle driver, I can see that the bind parameters are actually sent in the wrong order

For some reason, this error does not appear in an earlier insert statement with another CLOB...

INSERT INTO "EQDB_QUOTEBOM2" (id, message_id, opportunity_id, quote_id, sender, username, date_received, date_uploaded, is_uploaded, quote_bom_name, quote_bom_xml, quote_bom_cpq_name, quote_bom_cpq_xml) VALUES (:id, :message_id, :opportunity_id, :quote_id, :sender, :username, :date_received, :date_uploaded, :is_uploaded, :quote_bom_name, :quote_bom_xml, :quote_bom_cpq_name, :quote_bom_cpq_xml) 2012-04-17 10:33:21,233 INFO sqlalchemy.engine.base.Engine {'username': '', 'quote_id': '', 'sender': '', 'quote_bom_name': 'quote_bom_test.xml', 'quote_bom_cpq_name': '', 'quote_bom_cpq_xml': '', 'message_id': '', 'quote_bom_xml': 'PD94bWwgdmVyc2lvbj0iMS4wIiBlbmNvZGluZz0iVVRGLTgiPz4KPHF1b3RlX2JvbT4KCTxtZXNzYWdlX2hlYWRlcj4KCQk8c2VuZGVyPnF1b3RlYm9teGxzPC9zZW5kZXI+CgkJPHJlY2VpdmVyPkVRREI8L3JlY2VpdmVyPgoJCTxtZXNzYWdlX2FjdGlvbj5jcmVhdGU8L21lc3NhZ2VfYWN0aW9uPgoJCTxtZXNzYWdlX2lkPjkyQkMyRjNGOTdGQzQ1RDM5MDkwRjY1RUUxQTJENDQ4PC9tZXNzYWdlX2lkPgoJCTx0aW1lc3RhbXA+MjcwMzEyMTMzNjA5PC90aW1lc3RhbXA+Cgk8L21lc3NhZ2VfaGVhZGVyPgoJPGhlYWRlcj4KCQk8b3BwaWQ+T1AtMDAzMzY4OTwvb3BwaWQ...

14k CLOB

...29yZGVyY29kZT4KCQkJCTxncm91cGluZz48L2dyb3VwaW5nPgoJCQkJPHNwZWNfbnVtYmVyPjwvc3BlY19udW1iZXI+CgkJCQk8Z2JhZGRxdHk+NzwvZ2JhZGRxdHk+CgkJCQk8Y3VzdGFkZHF0eT4wPC9jdXN0YWRkcXR5PgoJCQk8L2xpbmVfaXRlbT4KCQkJPGxpbmVfaXRlbT4KCQkJCTxvcmRlcmNvZGU+OTUwLTAwMDQ3LTAxPC9vcmRlcmNvZGU+CgkJCQk8Z3JvdXBpbmc+PC9ncm91cGluZz4KCQkJCTxzcGVjX251bWJlcj48L3NwZWNfbnVtYmVyPgoJCQkJPGdiYWRkcXR5PjE2PC9nYmFkZHF0eT4KCQkJCTxjdXN0YWRkcXR5PjA8L2N1c3RhZGRxdHk+CgkJCTwvbGluZV9pdGVtPgoJCTwvbGluZV9pdGVtcz4KCTwvZGV0YWlsPgo8L3F1b3RlX2JvbT4K', 'is_uploaded': 0, 'date_received': datetime.datetime(2012, 4, 17, 9, 33, 18, 209000), 'id': '2a5b6e7c-f8b7-4e86-83d1-c282f0199e67', 'date_uploaded': None, 'opportunity_id': ''}

And the error does not appear if I use a lower level cx_oracle approach...

cursor.execute("""UPDATE EQDB_QUOTEBOM2 SET quote_bom_cpq_xml=:quote_bom_cpq_xml WHERE EQDB_QUOTEBOM2.id=:EQDB_QUOTEBOM2_id""", {'EQDB_QUOTEBOM2_id': str(oid), 'quote_bom_cpq_xml': base64_xml}) eqdb_conn.commit()

This error seems to occur when using the Hibernate ORM as well...

ORA-24816 and Hibernate http://www.odi.ch/weblog/posting.php?posting=496

Is there a method of correctly ordering the bind parameters for LOB's when using SqlAlchemy?

Thanks, Brian M

[Mike Bayer]

When inserting LOB's in Oracle the CLOB bind parameter must be ordered last.

I've used oracle for many years and have never observed this - but I don't understand what this even means here. The statement is:

UPDATE "EQDB_QUOTEBOM2" SET quote_bom_cpq_xml=:quote_bom_cpq_xml WHERE "EQDB_QUOTEBOM2".id = :EQDB_QUOTEBOM2_id

does it mean, the "param" as rendered in the UPDATE ? There's only one param in the SET clause here, so it's already "last" there, and it's not possible for it to be physically after the "eqdb_quotebom2_id" parameter, because that's not the syntax of the UPDATE statement.

If on the other hand is means, the actual dictionary of bound parameters, that doesn't make much sense anyway as dictionaries in Python are not ordered, but also the repr() you're showing, the CLOB ''is still last'' in this particular dictionary anyway:

{'EQDB_QUOTEBOM2_id': '19600052-e2d2-41ba-950b-0fbde8d07567', 'quote_bom_cpq_xml': 'PD94bWwgdmVyc2lvbj0nMS4wJyBlbmNvZGluZz0nVVRGLTgnPz4KPHF1b3RlX2JvbV9jcHE+CiAgPG1lc3NhZ2VfaGVhZGVyPgogICAgPHNlbmRlcj5FUURCPC9zZW5kZXI+CiAgICA8cmVjZWl2ZXI+Q1BRPC9yZWNlaXZlcj4KICAgIDxtZXNzYWdlX2FjdGlvbj5jcmVhdGU8L21lc3NhZ2VfYWN0aW9uPgogICAgPG1lc3NhZ2VfaWQ+Yjc3MDU3MTMtYmI0MS00...

14k base64 CLOB

...jdXN0YWRkcXR5PjA8L2N1c3RhZGRxdHk+CiAgICAgIDwvbGluZV9pdGVtPgogICAgICA8bGluZV9pdGVtPgogICAgICAgIDxvcmRlcmNvZGU+OTUwLTAwMDQ3LTAxPC9vcmRlcmNvZGU+CiAgICAgICAgPHByb2R1Y3Q+Q1MyMDAwPC9wcm9kdWN0PgogICAgICAgIDxlbGVtZW50PjwvZWxlbWVudD4KICAgICAgICA8Z3JvdXBpbmc+PC9ncm91cGluZz4KICAgICAgICA8c3BlY19udW1iZXI+PC9zcGVjX251bWJlcj4KICAgICAgICA8Z2JhZGRxdHk+MTY8L2diYWRkcXR5PgogICAgICAgIDxjdXN0YWRkcXR5PjA8L2N1c3RhZGRxdHk+CiAgICAgIDwvbGluZV9pdGVtPgogICAgPC9saW5lX2l0ZW1zPgogIDwvZGV0YWlsPgo8L3F1b3RlX2JvbV9jcHE+Cg=='}

the usage you illustrate for cx_oracle is also exactly what SQLAlchemy does with cx_oracle.

Lastly I cannot even reproduce this. Note that you need to attach a fully working test script to bug reports here to save us time, but in this case I created one myself, and I cannot reproduce:

from sqlalchemy import *

from sqlalchemy.dialects.oracle import CLOB

m = MetaData()
t = Table("EQDB_QUOTEBOM2", m, Column("id", Integer), Column("quote_bom_cpq_xml", CLOB))

e = create_engine("oracle://scott:tiger@/xe", echo=True)

m.drop_all(e)
m.create_all(e)

root = "PD94bWwgdmVyc2lvbj0nMS4wJyBlbmNvZGluZz0nVVRGLTgnPz4KPHF1b3RlX2JvbV9jc"
fourteenk = root * (14000 / (len(root) - 1))
assert len(fourteenk) >= 14000

e.execute(t.insert(), id=1, quote_bom_cpq_xml="test")


params = {'foo':1, 'quote_bom_cpq_xml':fourteenk}

for i in range(10):
    e.execute(
            t.update().where(t.c.id==bindparam('foo')).
               values(quote_bom_cpq_xml=bindparam('quote_bom_cpq_xml')),
          params)

the script runs through ten iterations and does the UPDATE of 14K in a CLOB without issue. I tried adding another parameter after the CLOB, still runs fine:

from sqlalchemy import *

from sqlalchemy.dialects.oracle import CLOB

m = MetaData()
t = Table("EQDB_QUOTEBOM2", m, Column("id", Integer), Column("quote_bom_cpq_xml", CLOB), Column('extra', Integer))

e = create_engine("oracle://scott:tiger@/xe", echo=True)

m.drop_all(e)
m.create_all(e)

root = "PD94bWwgdmVyc2lvbj0nMS4wJyBlbmNvZGluZz0nVVRGLTgnPz4KPHF1b3RlX2JvbV9jc"
fourteenk = root * (14000 / (len(root) - 1))
assert len(fourteenk) >= 14000

e.execute(t.insert(), id=1, quote_bom_cpq_xml="test")

params = {'quote_bom_cpq_xml':fourteenk, 'foo':1, 'extra':5}

for i in range(10):
    e.execute(
            t.update().where(t.c.id==bindparam('foo')).
               values(
                 quote_bom_cpq_xml=bindparam('quote_bom_cpq_xml'),
                 extra=bindparam('extra')),
          params)

if you can't provide a script here you might need to ask on cx_oracle's list https://lists.sourceforge.net/lists/listinfo/cx-oracle-users as to what might be going on here. Perhaps Oracle version, OCI version, cx_oracle version is important. There's not a whole lot SQLAlchemy can do here especially without a reproducing test case.

[Former user]

(original author: bmacauley) Replying to zzzeek:

When inserting LOB's in Oracle the CLOB bind parameter must be ordered last.

I've used oracle for many years and have never observed this - but I don't understand what this even means here. The statement is:

{{{ UPDATE "EQDB_QUOTEBOM2" SET quote_bom_cpq_xml=:quote_bom_cpq_xml WHERE "EQDB_QUOTEBOM2".id = :EQDB_QUOTEBOM2_id }}}

does it mean, the "param" as rendered in the UPDATE ?

There's only one param in the SET clause here, so it's already "last" there, and it's not possible for it to be physically after >the "eqdb_quotebom2_id" parameter, because that's not the syntax of the UPDATE statement.

If on the other hand is means, the actual dictionary of bound parameters, that doesn't make much sense anyway as dictionaries in >Python are not ordered, but also the repr() you're showing, the CLOB ''is still last'' in this particular dictionary anyway:

Yes...i am talking about the dictionary of bound parameters.

The repr() does indeed show the CLOB as the last bind parameter...as I mentioned...

The SqlAlchemy? generated sql suggests that the bind parameter ordering is correct. On further investigation, using a trace file >on the oracle driver, I can see that the bind parameters are actually sent in the wrong order

However when I look at the oracle instant client trace file it tells a very different story...the :quote_bom_cpq_xml bind parameter is loaded before the :EQDB_QUOTEBOM2_id causing the error message

I have enclosed the following...

quote_bom_uploader_ng.py ...the main script database.py ...database package quote_bom_uploader_ng.log ...log of the script running with echo=True cli_2420.zip ...the Oracle instant client trace file

It's not really a working script, but it gives you a bit more information on what i am trying to do

{{{ {'EQDB_QUOTEBOM2_id': '19600052-e2d2-41ba-950b-0fbde8d07567', 'quote_bom_cpq_xml': 'PD94bWwgdmVyc2lvbj0nMS4wJyBlbmNvZGluZz0nVVRGLTgnPz4KPHF1b3RlX2JvbV9jcHE+CiAgPG1lc3NhZ2VfaGVhZGVyPgogICAgPHNlbmRlcj5FUURCPC9zZW5kZXI+CiAgICA8cmVjZWl2ZXI+Q1BRPC9yZWNlaXZlcj4KICAgIDxtZXNzYWdlX2FjdGlvbj5jcmVhdGU8L21lc3NhZ2VfYWN0aW9uPgogICAgPG1lc3NhZ2VfaWQ+Yjc3MDU3MTMtYmI0MS00...

14k base64 CLOB

...jdXN0YWRkcXR5PjA8L2N1c3RhZGRxdHk+CiAgICAgIDwvbGluZV9pdGVtPgogICAgICA8bGluZV9pdGVtPgogICAgICAgIDxvcmRlcmNvZGU+OTUwLTAwMDQ3LTAxPC9vcmRlcmNvZGU+CiAgICAgICAgPHByb2R1Y3Q+Q1MyMDAwPC9wcm9kdWN0PgogICAgICAgIDxlbGVtZW50PjwvZWxlbWVudD4KICAgICAgICA8Z3JvdXBpbmc+PC9ncm91cGluZz4KICAgICAgICA8c3BlY19udW1iZXI+PC9zcGVjX251bWJlcj4KICAgICAgICA8Z2JhZGRxdHk+MTY8L2diYWRkcXR5PgogICAgICAgIDxjdXN0YWRkcXR5PjA8L2N1c3RhZGRxdHk+CiAgICAgIDwvbGluZV9pdGVtPgogICAgPC9saW5lX2l0ZW1zPgogIDwvZGV0YWlsPgo8L3F1b3RlX2JvbV9jcHE+Cg=='} }}}

the usage you illustrate for cx_oracle is also exactly what SQLAlchemy does with cx_oracle.

Lastly I cannot even reproduce this. Note that you need to attach a fully working test script to bug reports here to save us time, but in this case I created one myself, and I cannot reproduce:

Not sure if it matters, but I was using sqlalchemy orm rather than sqlalchemy core

{{{ from sqlalchemy import *

from sqlalchemy.dialects.oracle import CLOB

m = MetaData() t = Table("EQDB_QUOTEBOM2", m, Column("id", Integer), Column("quote_bom_cpq_xml", CLOB))

e = create_engine("oracle://scott:tiger@/xe", echo=True)

m.drop_all(e) m.create_all(e)

root = "PD94bWwgdmVyc2lvbj0nMS4wJyBlbmNvZGluZz0nVVRGLTgnPz4KPHF1b3RlX2JvbV9jc" fourteenk = root * (14000 / (len(root) - 1)) assert len(fourteenk) >= 14000

e.execute(t.insert(), id=1, quote_bom_cpq_xml="test")

params = {'foo':1, 'quote_bom_cpq_xml':fourteenk}

for i in range(10): e.execute( t.update().where(t.c.id==bindparam('foo')). values(quote_bom_cpq_xml=bindparam('quote_bom_cpq_xml')), params)

}}}

the script runs through ten iterations and does the UPDATE of 14K in a CLOB without issue. I tried adding another parameter after the CLOB, still runs fine:

{{{ from sqlalchemy import *

from sqlalchemy.dialects.oracle import CLOB

m = MetaData() t = Table("EQDB_QUOTEBOM2", m, Column("id", Integer), Column("quote_bom_cpq_xml", CLOB), Column('extra', Integer))

e = create_engine("oracle://scott:tiger@/xe", echo=True)

m.drop_all(e) m.create_all(e)

root = "PD94bWwgdmVyc2lvbj0nMS4wJyBlbmNvZGluZz0nVVRGLTgnPz4KPHF1b3RlX2JvbV9jc" fourteenk = root * (14000 / (len(root) - 1)) assert len(fourteenk) >= 14000

e.execute(t.insert(), id=1, quote_bom_cpq_xml="test")

params = {'quote_bom_cpq_xml':fourteenk, 'foo':1, 'extra':5}

for i in range(10): e.execute( t.update().where(t.c.id==bindparam('foo')). values( quote_bom_cpq_xml=bindparam('quote_bom_cpq_xml'), extra=bindparam('extra')), params)

}}}

if you can't provide a script here you might need to ask on cx_oracle's list https://lists.sourceforge.net/lists/listinfo/cx-oracle-users as to what might be going on here. Perhaps Oracle version, OCI version, cx_oracle version is important. There's not a whole lot SQLAlchemy can do here especially without a reproducing test case.

I'm not sure what I would ask the cx_oracle list...it seems to work ok when using the lower level cx_oracle...see database.py

[Mike Bayer]

Replying to bmacauley:

I'm not sure what I would ask the cx_oracle list...it seems to work ok when using the lower level cx_oracle...see database.py

you would ask them how you could be getting ORA-24816 when emitting a query. SQLAlchemy cannot control the order of binds as passed to OCI. The DBAPI asks for a dictionary of bound parameters, and in python, dictionaries are unordered. cx_oracle needs to take the steps here to ensure that it sends LOB values to OCI last, if this is indeed a requirement.

If there is some advice that cx_oracle has for SQLAlchemy here, we will take it. But as I said, there is no way I'm aware of to control this ordering from the DBAPI interface. The .trc file here is something Anthony Tuniga might know how to read.

Are you using a very old version of cx_oracle perhaps ?

[Mike Bayer]

also what happens when you run the test script I have above ? that would smoke out versioning issues.

[Former user]

(original author: bmacauley) Replying to zzzeek:

Replying to bmacauley:

I'm not sure what I would ask the cx_oracle list...it seems to work ok when using the lower level cx_oracle...see database.py

you would ask them how you could be getting ORA-24816 when emitting a query. SQLAlchemy cannot control the order of binds as passed to OCI. The DBAPI asks for a dictionary of bound parameters, and in python, dictionaries are unordered. cx_oracle needs to take the steps here to ensure that it sends LOB values to OCI last, if this is indeed a requirement.

If there is some advice that cx_oracle has for SQLAlchemy here, we will take it. But as I said, there is no way I'm aware of to control this ordering from the DBAPI interface. The .trc file here is something Anthony Tuniga might know how to read.

Are you using a very old version of cx_oracle perhaps ?

Nope...using the very latest version cx_oracle 5.1.1

[Mike Bayer]

I've asked on the cx_oracle list, will add an archive link here once it shows up.

[Former user]

(original author: bmacauley) Replying to zzzeek:

I've asked on the cx_oracle list, will add an archive link here once it shows up.

Ok..cheers

I was just about to raise a ticket over there

[Mike Bayer]

so this is what happens with cx_oracle sometimes, you just don't get a response for awhile.

Here's the archive link:

http://sourceforge.net/mailarchive/forum.php?thread_name=18BDBAF0-A477-404A-B89F-4C63280D898F%40zzzcomputing.com&forum_name=cx-oracle-users

and in the meantime, what happens if you run my test script above ?

[Mike Bayer]

this may or may not be fixed by #2561 and d3e60f805d573206f68c0eb7e7187c5a1c54a731.

[Volodymyr Vladymyrov]

Just wanted to confirm that the fix works for me. Faced same Oracle exception recently: "(DatabaseError) ORA-24816: Expanded non LONG bind data supplied after actual LONG or LOB column" Updated SQLAlchemy from 0.7.2 to 0.7.10 and tested again - same insert query worked fine and LONG value was inserted into the table.

[Mike Bayer]

hooray ! you wouldn't happen to have a self-contained way to reproduce ? would love to add a test.

otherwise thanks for commenting!

[Volodymyr Vladymyrov]

Sure there is a way to localize and reproduce. I'll try to write such test and will add it to sqlalchemy. Would love to do this myself as practice exercise. Will keep you posted.

[Mike Bayer]

Removing milestone: 0.8.xx (automated comment)