New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

ORA-24816 with cx_Oracle 6.0rc1 #50

Closed
mlafon opened this Issue Jul 20, 2017 · 9 comments

Comments

Projects
None yet
3 participants
@mlafon
Copy link

mlafon commented Jul 20, 2017

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

This comment has been minimized.

Copy link
Member

anthony-tuininga commented Jul 20, 2017

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

@cjbj

This comment has been minimized.

Copy link
Member

cjbj commented Jul 20, 2017

@mlafon Can you give us your testcase?

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

@mlafon

This comment has been minimized.

Copy link
Author

mlafon commented Jul 21, 2017

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

@cjbj cjbj added bug and removed question labels Jul 24, 2017

@mlafon

This comment has been minimized.

Copy link
Author

mlafon commented Jul 25, 2017

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

This comment has been minimized.

Copy link
Member

cjbj commented Jul 25, 2017

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

This comment has been minimized.

Copy link
Author

mlafon commented Jul 25, 2017

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

This comment has been minimized.

Copy link
Member

anthony-tuininga commented Jul 25, 2017

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 added a commit that referenced this issue Jul 25, 2017

In certain cases where non-long values follow LOB values in the list …
…of bind

variables, the error "ORA-24816: Expanded non LONG bind data supplied after
actual LONG or LOB column" is raised; this occurs if the length of the supplied
value, when expanded, might exceed 4000 bytes
(#50).
@anthony-tuininga

This comment has been minimized.

Copy link
Member

anthony-tuininga commented Jul 25, 2017

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

@mlafon

This comment has been minimized.

Copy link
Author

mlafon commented Jul 26, 2017

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment