ORA-24816 with cx_Oracle 6.0rc1

[mlafon]

When using cx_Oracle 6.0rc1, I encounter the ORA-24816 DatabaseError on specific queries:

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

The query does not seem special and the error is only triggered inside Django (not when it is run from the command line, even if the code is the same) so I think this may be related to previous queries, the context or configuration settings.

I have the same error when using Oracle Client 11.2, 12.1 or 12.2.
I have the same error when using current master (3243261)
I don't have the error when using cx_Oracle 5.3.

The error description is somewhat cryptic for me, so I don't know how to continue the analysis.

Is there a reason it is now raised by cx_Oracle 6.0rc1? What information can be retrieved to diagnose this issue?

[anthony-tuininga]

I've never seen that problem myself! I'll ask internally about what might cause that problem and get back to you.

[cjbj]

@mlafon Can you give us your testcase?

It's likely similar to https://bugs.php.net/bug.php?id=72524

[mlafon]

I have finally achieved to reproduce the problem with only a few queries.

Arguments order and content is important in my case, so I hope you can also reproduce it on your side with the following script.

import cx_Oracle

dsn = '..FILLME..'
conn = cx_Oracle.connect(dsn)
cursor = conn.cursor()

print 'cx_Oracle %s' % cx_Oracle.version
print 'clientversion: %s' % '.'.join([str(d) for d in cx_Oracle.clientversion()])

for table in ['X_TABLE1', 'X_TABLE2']:
    try:
        cursor.execute('DROP TABLE "%s" CASCADE CONSTRAINTS PURGE' % table)
    except:
        print 'E', table
        pass

print 'CREATE TABLE X_TABLE1'
cursor.execute(' \
  CREATE TABLE "X_TABLE1" ( \
    "ID" NUMBER(11) PRIMARY KEY, \
    "STR1" NVARCHAR2(256) DEFAULT \'\' NULL, \
    "STR2" NVARCHAR2(256) DEFAULT \'\' NULL, \
    "STR3" NVARCHAR2(256) DEFAULT \'\' NULL, \
    "NCLOB1" NCLOB DEFAULT \'\' NULL, \
    "NCLOB2" NCLOB DEFAULT \'\' NULL \
  ) \
')

print 'CREATE TABLE X_TABLE2'
cursor.execute(' \
  CREATE TABLE "X_TABLE2" ( \
    "ID" NUMBER(11) NOT NULL PRIMARY KEY, \
    "VALUE" NUMBER(11) NOT NULL \
  ) \
')

id = cursor.var(cx_Oracle.NUMBER)
print 'INSERT INTO X_TABLE1'
cursor.execute(' \
  INSERT INTO "X_TABLE1" ("ID", "STR2", "STR3", "NCLOB1", "NCLOB2", "STR1") \
  VALUES (:arg0, :arg1, :arg2, :arg3, :arg4, :arg5) \
  RETURNING "X_TABLE1"."ID" INTO :arg6',
  [1, '555a4c78', 'f319ef0e', '23009914', '', '', id])
print 'new id is %s' % id

print 'INSERT INTO X_TABLE2'
cursor.execute('INSERT INTO "X_TABLE2" ("ID", "VALUE") VALUES (:arg0, :arg1)', [0, 1])

id = cursor.var(cx_Oracle.NUMBER)
print 'INSERT INTO X_TABLE1'
cursor.execute(' \
  INSERT INTO "X_TABLE1" ("ID", "STR2", "STR3", "NCLOB1", "NCLOB2", "STR1") \
  VALUES (:arg0, :arg1, :arg2, :arg3, :arg4, :arg5) \
  RETURNING "X_TABLE1"."ID" INTO :arg6',
  [2, u'd5ff845a', u'94275767', u'bf161ff6', u'', u'', id])
print 'new id is %s' % id

[mlafon]

Have you achieved to reproduce the issue using my test script?

Note that using non-unicode strings for empty strings in the last query does not trigger the issue. It's maybe related to the encoding of unicode empty strings.

[cjbj]

I haven't had a chance, but have not forgotten. Did you try replacing the empty strings with values to see if the base problem is similar to the PHP one?

[mlafon]

Yes, the problem is also not reproduced with non-empty strings. In fact, if only one of the two unicode empty strings is either non-empty or non-unicode, the issue is not triggered.

I have tried to investigate the issue but I don't know exactly what to search, as I don't understand the Oracle error message and I don't understand why the previous two queries are important to trigger the issue.

The only thing I have found is that the variable size is set to 4000 (default size of vt_String) due to size being 0 (empty) in Variable_New. This does not seem to be the case in 5.3 but I'm not sure this is a problem.

[anthony-tuininga]

I am able to replicate your issue -- but only if the database is running 12.1. If it is running 12.2 the problem does not occur. I'll look into it further. Thanks for providing the test case!

[anthony-tuininga]

Change made so that null values do not trigger this situation.

[mlafon]

Fix successfully verified on my side. Thanks for your support.