How to use in and out bind variables with executemany in cx_Oracle in Python

Question

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': }]

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': }]
['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': }]
DML row count: [1, 1, 1]
[{'kex': 'A', 'rid': 'K'}, {'kex': 'B', 'rid': 'L'}, {'kex': 'C', 'rid': }]
[['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?

UPDATE2

I add the simplistic test Perl script (Perl: v5.26.2, DBI: 1.641, I have tried Oracle 12c client vs 12c server, 12c client vs 19 server, 19 client vs 19 server). Here in the INSERT the input and output bind variable both goes through :2. The only trick is that an array has to be bound to each bind variable! It does not work with named placeholders, but it does work with numbered placeholders!

use strict; use warnings;

use DBI qw(:sql_types); #use DBD::Oracle qw(:ora_types);
use Data::Dumper;
$Data::Dumper::Indent = 0;

my ($srv, $usr, $pwd) = @ARGV;
my $dbh = DBI->connect("dbi:Oracle:$srv", $usr, $pwd, {AutoCommit=>0, RaiseError=>0});

my $table = 'mytable';
{ my $sql = "CREATE TABLE $table (kex VARCHAR2(20 BYTE) NOT NULL ENABLE)";
my $sth = $dbh->prepare($sql) or warn $dbh->errstr;
my $rv = $sth->execute or warn $sth->errstr; }

my $sql = "INSERT INTO $table VALUES (:1 || :2) RETURNING rowid INTO :2";
my $sth = $dbh->prepare($sql) or die $dbh->errstr;

my @bind = ( ['A', 'B', 'C'], ['K', 'L', 'M'],);
$sth->bind_param_array(1, $bind[0]);
$sth->bind_param_inout_array(2, $bind[1], 200); #, {ora_type => ORA_VARCHAR2});
print "Before exec ", Dumper(\@bind), "
";
my @rv = $sth->execute_array({}) or die $sth->errstr;
print "returned (tuple, rows) = @rv
";
print "After exec ", Dumper(\@bind), "
";

$sth = $dbh->prepare("SELECT t.*, rowid FROM $table t") or die $sth->errstr;
my $rv = $sth->execute() or die $sth->errstr;
$rv = $sth->fetchall_arrayref;
print "Returned data", Dumper($rv), "
";
$dbh->rollback;

Called with SRV USER PASSWD command line arguments, the result is:

Before exec $VAR1 = [['A','B','C'],['K','L','M']];
returned (tuple, rows) = 3 3
After exec $VAR1 = [['A','B','C'],['AABVczAD1AALvTlAAA','AABVczAD1AALvTlAAB','AABVczAD1AALvTlAAC']];
Returned data$VAR1 = [['AK','AABVczAD1AALvTlAAA'],['BL','AABVczAD1AALvTlAAB'],['CM','AABVczAD1AALvTlAAC']];

user_5019309 · Answer

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

user_4799035 · Answer

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')

How to use in and out bind variables with executemany in cx_Oracle in Python

2 Answers