How to use in and out bind variables with executemany in cx

I would like to use cx_Oracle with in and out bind variables with named placeholders with Cursor.exeutemany. After a lot of browsing and cul-de-sac-s I could not find a proper way.

This is what I could figure out:

I have created a pretty simple table with one column:

CREATE TABLE mytable (kex VARCHAR2(20 BYTE) NOT NULL ENABLE)

The INSERT also simple. It requires two named placeholders: one for input only (called 'kex') and an input-output ('rid') bind variable:

INSERT INTO mytable VALUES (:kex || :rid) RETURNING rowid INTO :rid

Then I created the bind array with 3 bind-sets:

N = 3
bind = [dict(kex = chr(65 i), rid = chr(75 i)) for i in range(N)]
print('Before set', bind)

Output is:

Before set [{'kex': 'A', 'rid': 'K'}, {'kex': 'B', 'rid': 'L'}, {'kex': 'C', 'rid': 'M'}]

Then I modified the last bind-set's rid value to contain a vector of variables:

conn = cx_Oracle.connect(mydbconn)
curs = conn.cursor()

# Define the bind variable array
var = curs.var(str, size = 50, arraysize = N)
bind[-1]['rid'] = var     # Why the final bind-set has to be modified?
# Set the input values in the bind variable array
for i in range(N): var.setvalue(i, chr(97 i)) # To-be commented!!!
print('After set', bind)

Output:

After set [{'kex': 'A', 'rid': 'K'}, {'kex': 'B', 'rid': 'L'}, {'kex': 'C', 'rid': <cx_Oracle.Var of type DB_TYPE_VARCHAR with value ['a', 'b', 'c']>}]

Then executemany the whole bind-set:

sql_txt = "INSERT INTO mytable VALUES (:kex || :rid) RETURNING rowid INTO :rid"
rv = curs.executemany(sql_txt, bind, batcherrors = True
    , arraydmlrowcounts = True)
for i in curs.getbatcherrors(): print('Batch err:', i)
print('DML row count:', [i for i in curs.getarraydmlrowcounts()])
print(bind)
print([var.getvalue(i) for i in range(N)])

Output:

DML row count: [1, 1, 1]
[{'kex': 'A', 'rid': 'K'}, {'kex': 'B', 'rid': 'L'}, {'kex': 'C', 'rid': <cx_Oracle.Var of type DB_TYPE_VARCHAR with value ['a', 'b', 'c']>}]
['a', 'b', 'c']

Finally the inserted content is printed:

for i in curs.execute(f"SELECT t.*, rowid FROM mytable t"): print('Ret', i)

Output:

Ret ('Aa', 'ABf0HwAMlAAPmOLAAA')
Ret ('Bb', 'ABf0HwAMlAAPmOLAAB')
Ret ('Cc', 'ABf0HwAMlAAPmOLAAC')

If I simply comment the var.setvalue(i, ...) line from the above code, then I get back the output rowids, but it seems the :rid as an input bind value become NULL somehow.

Complete output without setvalue:

Before set [{'kex': 'A', 'rid': 'K'}, {'kex': 'B', 'rid': 'L'}, {'kex': 'C', 'rid': 'M'}]
After set [{'kex': 'A', 'rid': 'K'}, {'kex': 'B', 'rid': 'L'}, {'kex': 'C', 'rid': <cx_Oracle.Var of type DB_TYPE_VARCHAR with value [None, None, None]>}]
DML row count: [1, 1, 1]
[{'kex': 'A', 'rid': 'K'}, {'kex': 'B', 'rid': 'L'}, {'kex': 'C', 'rid': <cx_Oracle.Var of type DB_TYPE_VARCHAR with value [['ABf0HxAMlAAPmOOAAA'], ['ABf0HxAMlAAPmOOAAB'], ['ABf0HxAMlAAPmOOAAC']]>}]
[['ABf0HxAMlAAPmOOAAA'], ['ABf0HxAMlAAPmOOAAB'], ['ABf0HxAMlAAPmOOAAC']]
Ret ('A', 'ABf0HxAMlAAPmOOAAA')
Ret ('B', 'ABf0HxAMlAAPmOOAAB')
Ret ('C', 'ABf0HxAMlAAPmOOAAC')

So, if setvariable is used, then rid behaves as simple input bind variable (but setvariable overrides the original values of rid). If setvariable is removed, then rid behaves as an output variable. But it seems it cannot work as input-output bind variable.

UPDATE

In Perl I have tried the bind_param_inout_array defined in DBD::Oracle. It can use the ? and :1 bind notation and it works perfectly with in-out variables! I tried with INSERT INTO mytable VALUES (:1 || :2) RETURNING rowid INTO :2. In this case for :1 a vector must be applied using bind_param_array and for :2 also a vector must be applied using bind_param_inout_array.

I also tried to do similar things in Python. But even is I use

val = curs.var(str, size=50)
val.setvalue(0, 'a') # Maybe commented
execute('INSERT INTO mytable VALUES (:1 || :2) RETURNING rowid INTO :2'
    , {'1': 'A', '2': val})

it does work only in one direction! If setvalue is applied for Cursor.Val object, then it is used as an output bind variable. If setvalue is not used, then it is an input bind variable (using NULL as input value). It seems Oracle passes and and retrieves values through this variable, but Python is not ready to handle it for some reason.

Does anyone know the solution?

CodePudding user response：

https://cx-oracle.readthedocs.io/en/latest/user_guide/bind.html#dml-returning-bind-variables
From doc.

No duplicate binds are allowed in a DML statement with a RETURNING clause, and no duplication is allowed between bind variables in the DML section and the RETURNING section of the statement.

Thats means insert into table (:1) returning into :1 is illegal.

Try to wrap around your statement with an anonymous block. You might use something like this.

Blockquote

Declare
var1 varchar2(100);
var2 varchar2(100);
begin
var1 := :1;
var2 := :2;
INSERT INTO mytable VALUES (var1 || var2) RETURNING rowid INTO var2;
:2 := var2; 
end:

Note. I haven't tested it. :) I don't have a handy oracle instance

CodePudding user response：

As mentioned in the other answer, you have to change a bind variable name.

There is an example of DML RETURNING with executemany() in the cx_Oracle doc executemany() DML RETURNING.

With your schema and data, a working script is:

import cx_Oracle as oracledb
import os
import platform

if platform.system() == "Darwin":
    oracledb.init_oracle_client(lib_dir=os.environ.get("HOME") "/Downloads/instantclient_19_8")
elif platform.system() == "Windows":
     cx_Oracle.init_oracle_client(lib_dir=r"C:\oracle\instantclient_19_14")

username = os.environ.get("PYTHON_USERNAME")
password = os.environ.get("PYTHON_PASSWORD")
connect_string = os.environ.get("PYTHON_CONNECTSTRING")

connection = oracledb.connect(user=username, password=password, dsn=connect_string)

with connection.cursor() as cursor:
    try:

        N = 3
        bind = [dict(kex = chr(65 i), rid = chr(75 i)) for i in range(N)]
        print('Before set', bind)

        rowidout = cursor.var(oracledb.DB_TYPE_ROWID, arraysize = N)
        cursor.setinputsizes(kex=20, rid=20, ridoutbv=rowidout)  # actual kex   rid data sizes should be < col width

        sql = "INSERT INTO mytable VALUES (:kex || :rid) RETURNING rowid INTO :ridoutbv"
        cursor.executemany(sql, bind, batcherrors = True, arraydmlrowcounts = True)
        for i in cursor.getbatcherrors():
            print('Batch err:', i)

        print('DML row count:', [i for i in cursor.getarraydmlrowcounts()])
        print(bind)
        print([rowidout.getvalue(i) for i in range(N)])

        for i in cursor.execute(f"SELECT t.*, rowid FROM mytable t"):
            print('Ret', i)

    except oracledb.Error as e:
        error, = e.args
        print(error.message)
        print(sql)
        if (error.offset):
            print('^'.rjust(error.offset 1, ' '))

Output is like:

Before set [{'kex': 'A', 'rid': 'K'}, {'kex': 'B', 'rid': 'L'}, {'kex': 'C', 'rid': 'M'}]
DML row count: [1, 1, 1]
[{'kex': 'A', 'rid': 'K'}, {'kex': 'B', 'rid': 'L'}, {'kex': 'C', 'rid': 'M'}]
[['AAAbj AAMAAABwuAAA'], ['AAAbj AAMAAABwuAAB'], ['AAAbj AAMAAABwuAAC']]
Ret ('AK', 'AAAbj AAMAAABwuAAA')
Ret ('BL', 'AAAbj AAMAAABwuAAB')
Ret ('CM', 'AAAbj AAMAAABwuAAC')