cx_Oracle 6.3 regression IndexError: cxoVar_getSingleValue: array size exceeded

[zzzeek]

output in cx_Oracle 6.2 and below for variable.getvalue() when there's no value, None. in 6.3 I am getting the above error "IndexError: cxoVar_getSingleValue: array size exceeded".

import cx_Oracle

c = cx_Oracle.connect(
    dsn=cx_Oracle.makedsn("oracle1120", 1521), user="scott", password="tiger")

cursor = c.cursor()

try:
    cursor.execute("drop table foo")
except:
    pass

cursor.execute("""
    create table foo(
        id integer primary key,
        data varchar(50)
    )
""")

cursor.execute("insert into foo (id, data) values (1, 'hi')")

var = cursor.var(cx_Oracle.STRING)

cursor.execute("""
    UPDATE foo set data='there' WHERE id=2 RETURNING foo.id INTO :ret_0
""", {"ret_0": var})

print(var.getvalue())

[anthony-tuininga]

DML returning was changed in cx_Oracle 6.3 to support cursor.executemany(). As such, you should be adding this code in cx_Oracle 6.3 and up (its safe in 6.2 and up but won't do anything in 6.2).

cx_Oracle.__future__.dml_ret_array_val = True

If you add this code you will get back an empty array instead of None. This is what cx_Oracle 7 will do all of the time. I'll get back to you once I figure out what is causing this issue!

[anthony-tuininga]

I've added a commit that addresses this issue. I'd recommend adding the code I mentioned earlier, however! Let me know if it addresses the situation for you, too.

[zzzeek]

as far as the __future__ thing, if it means I get back a [] instead of None, and then later I'll always get a [], none of that matters much as I can just get my returning logic to handle [] / None the same way (if it doesn't already), I just need it to not raise a weird IndexError :)

[zzzeek]

also, so strange that you are getting RETURNING to work with executemany() (which would be an unusual feature that for example pyodbc and psycopg2 don't do at all), though AFAICT RETURNING on Oracle doesn't work with an UPDATE or DELETE that matches on multiple rows (which is what all other drivers can do). I'd prefer if it worked with the latter.

[cjbj]

@zzzeek Scenario 3 in https://blogs.oracle.com/opal/efficient-and-scalable-batch-statement-execution-in-python-cx_oracle shows DML RETURNING for DELETE of multiple rows with executemany().

[anthony-tuininga]

as far as the future thing, if it means I get back a [] instead of None, and then later I'll always get a [], none of that matters much as I can just get my returning logic to handle [] / None the same way (if it doesn't already), I just need it to not raise a weird IndexError :)

Its not quite that simple, naturally! If you execute a DML returning statement once and it returns one or more rows and you execute a second time and it returns no rows, cx_Oracle 6.2 will return the first row of those returned for the first execution where cx_Oracle 6.3.1 (when released!) will return None consistently now.

also, so strange that you are getting RETURNING to work with executemany() (which would be an unusual feature that for example pyodbc and psycopg2 don't do at all), though AFAICT RETURNING on Oracle doesn't work with an UPDATE or DELETE that matches on multiple rows (which is what all other drivers can do). I'd prefer if it worked with the latter.

DML returning with executemany() is not a common feature, I agree, but it can be useful in certain situations! And as Chris noted, DML returning works just fine with an update or delete that returns multiple rows. In cx_Oracle 6.2 and in cx_Oracle 6.3 with cx_Oracle.__future__.dml_ret_array_val not set or False you need to use var.values to get the full set of values that have been returned. In cx_Oracle 6.3 with cx_Oracle.__future__.dml_ret_array_val set to True you can use the regular var.getvalue() which will return the array as expected. Perhaps that is the source of the confusion?

As hinted above I plan to release cx_Oracle 6.3.1 with this fix included in it. If you can verify that it resolves your issue I'd appreciate it. Thanks!

[zzzeek]

Its not quite that simple, naturally! If you execute a DML returning statement once and it returns one or more rows and you execute a second time and it returns no rows, cx_Oracle 6.2 will return the first row of those returned for the first execution where cx_Oracle 6.3.1 (when released!) will return None consistently now.

this sounds very strange. Is this a known bug in 6.2 (and if so what is the issue number?) I'm confused when you say "execute a second time". We don't have a "prepared statement" concept here, how would cx_Oracle have any idea I'm executing the same statement twice? Do you mean, that I use the same cursor.var() a second time in a second statement execution? Or just using the same cursor?

DML returning with executemany() is not a common feature, I agree, but it can be useful in certain situations!

agree, but since nobody supports it, SQLAlchemy doesn't either :)

And as Chris noted, DML returning works just fine with an update or delete that returns multiple rows.

Yes, in actuality what I'm referring towards is the fact that to use RETURNING at all in Oracle, you need to use OUT parameters, which SQLAlchemy needs to internally convert into result sets to work like all the other databases. I was likely not aware, or perhaps this is new in the cx_Oracle 6.x series, that there's a .values accessor that achieves this. Still, to make RETURNING work like other databases, e.g. that you just get a result set, I have to jump through some hoops (see https://github.com/zzzeek/sqlalchemy/blob/master/lib/sqlalchemy/dialects/oracle/cx_oracle.py#L525). This logic is likely something I should rewrite for cx_Oracle (though I just got done with a cx_Oracle rewrite for the 6.x series, need a year off :) )

In cx_Oracle 6.2 and in cx_Oracle 6.3 with cx_Oracle.future.dml_ret_array_val not set or False you need to use var.values to get the full set of values that have been returned. In cx_Oracle 6.3 with cx_Oracle.future.dml_ret_array_val set to True you can use the regular var.getvalue() which will return the array as expected. Perhaps that is the source of the confusion?

nothing in SQLAlchemy is expecting var.getvalue() to return a list at this point and the library is unaware of the var.values accessor. All of our OUT / RETURNING support for cx_Oracle deals with zero or one row right now. Keep in mind I'm still supporting the cx_Oracle 5.x series as well so it's a little intricate to push forward with all these backwards-incompatible changes while still supporting the old versions. Since cx_Oracle's velocity seems very high these days, it is possible I can just drop cx_Oracle 5.x support in SQLAlchemy 1.3, so perhaps that will be where this happens.

[anthony-tuininga]

Its not quite that simple, naturally! If you execute a DML returning statement once and it returns one or more rows and you execute a second time and it returns no rows, cx_Oracle 6.2 will return the first row of those returned for the first execution where cx_Oracle 6.3.1 (when released!) will return None consistently now.

this sounds very strange. Is this a known bug in 6.2 (and if so what is the issue number?) I'm confused when you say "execute a second time". We don't have a "prepared statement" concept here, how would cx_Oracle have any idea I'm executing the same statement twice? Do you mean, that I use the same cursor.var() a second time in a second statement execution? Or just using the same cursor?

Actually, cx_Oracle does have a "prepared statement" concept. See here and here. Note the third paragraph of the second reference. And yes, it has more to do with reuse of the variable than with executing the same statement, but executing the same statement with the same variable is likely the scenario that people would encounter this. Nobody has reported this in cx_Oracle 6.2, likely because it is not a common scenario. I simply noticed it myself when looking into this issue.

And as Chris noted, DML returning works just fine with an update or delete that returns multiple rows.

Yes, in actuality what I'm referring towards is the fact that to use RETURNING at all in Oracle, you need to use OUT parameters, which SQLAlchemy needs to internally convert into result sets to work like all the other databases. I was likely not aware, or perhaps this is new in the cx_Oracle 6.x series, that there's a .values accessor that achieves this. Still, to make RETURNING work like other databases, e.g. that you just get a result set, I have to jump through some hoops (see https://github.com/zzzeek/sqlalchemy/blob/master/lib/sqlalchemy/dialects/oracle/cx_oracle.py#L525). This logic is likely something I should rewrite for cx_Oracle (though I just got done with a cx_Oracle rewrite for the 6.x series, need a year off :) )

The addition of the var.values property is indeed something new for cx_Oracle 6. Before that I assumed that one or zero rows could be returned by a DML returning statement. Thankfully it is a simple matter of just replacing var.getvalue() with var.values and the rest of your logic should work just fine -- with the caveat that var.values() returns an array of values and var.getvalue() will return a scalar value. And the additional caveat that in cx_Oracle 7 (and cx_Oracle 6.3 with cx_Oracle.__future__.dml_ret_array_val = True) var.values will return an array of arrays in order to handle DML returning with executemany() properly.

In cx_Oracle 6.2 and in cx_Oracle 6.3 with cx_Oracle.__future__.dml_ret_array_val not set or False you need to use var.values to get the full set of values that have been returned. In cx_Oracle 6.3 with cx_Oracle.__future__.dml_ret_array_val set to True you can use the regular var.getvalue() which will return the array as expected. Perhaps that is the source of the confusion?

nothing in SQLAlchemy is expecting var.getvalue() to return a list at this point and the library is unaware of the var.values accessor. All of our OUT / RETURNING support for cx_Oracle deals with zero or one row right now. Keep in mind I'm still supporting the cx_Oracle 5.x series as well so it's a little intricate to push forward with all these backwards-incompatible changes while still supporting the old versions. Since cx_Oracle's velocity seems very high these days, it is possible I can just drop cx_Oracle 5.x support in SQLAlchemy 1.3, so perhaps that will be where this happens.

Yes, up until cx_Oracle 6 I assumed that DML returning was only capable of handling zero or one row, too. :-) I suspect you can drop support for cx_Oracle 5.x since downloads for cx_Oracle 5.x are now less than 10% of all downloads of cx_Oracle. And if there is something that can be done to make your life simpler, please let me know!

[anthony-tuininga]

I've released cx_Oracle 6.3.1 correcting this issue. I'm sure you'll let me know if something else turns up. :-)