After using function setinputsizes(), binding variables values are misplaced in executemany()

[tapurox]

1. What versions are you using?

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

platform.platform: Windows-10-10.0.17763-SP0 sys.maxsize > 2**32: True platform.python_version: 3.6.2

cx_Oracle.version: 8.2.1 cx_Oracle.clientversion: (12, 2, 0, 1, 0)

2. Is it an error or a hang or a crash?

It is an error.

3. What error(s) or behavior you are seeing?

After using cursor setinputsizes() function, variable values are misplaced in executemany() so data write to database are invalid. Using cursor executemany() with default array size works well.

4. Include a runnable Python script that shows the problem.

Sql to create table: CREATE TABLE EXCEL_TMP (
"A" VARCHAR2(250 BYTE), "B" VARCHAR2(250 BYTE), "C" VARCHAR2(250 BYTE), "D" VARCHAR2(250 BYTE), "E" VARCHAR2(250 BYTE), "F" VARCHAR2(250 BYTE), "G" VARCHAR2(250 BYTE), "H" VARCHAR2(250 BYTE), "I" VARCHAR2(250 BYTE), "J" VARCHAR2(250 BYTE), "K" VARCHAR2(250 BYTE), "L" VARCHAR2(250 BYTE), "M" VARCHAR2(250 BYTE), "N" VARCHAR2(250 BYTE), "O" VARCHAR2(250 BYTE), "P" VARCHAR2(250 BYTE), "Q" VARCHAR2(250 BYTE), "R" VARCHAR2(250 BYTE), "S" VARCHAR2(250 BYTE), "T" VARCHAR2(250 BYTE) );

# -*- coding: utf-8 -*-

import cx_Oracle

def write_data():
    sql = "insert into EXCEL_TMP (A, B, C, D, E, F, G, H, I, J, K, L, M, N, O, P, Q, R, S, T) \
            values (:v0, :v1, :v2, :v3, :v4, :v5, :v6, :v7, :v8, :v9, :v10, :v11, :v12, :v13, :v14, :v15, :v16, :v17, :v18, :v19)"
    login = "login"
    password = "pass"
    tns="tns"
    batch_size = 10000

    test_data = [
                ('XXXXXXXXa', 'XXXXXXXXa', 'XXXXXXXXa', 'Pracownik', 'Biuro', '2018-07-19 12:54:33', None, None, None, None, '2018-07-19 00:00:00', None, None, 'Nadanie', '000000', 'DB - MART_TABLE_ALL, MART_VIEW_ALL;', None, None, None, None), 
                ('XXXXXXXXb', 'XXXXXXXXb', 'XXXXXXXXb', 'Pracownik', 'Biuro', '2018-07-19 12:38:18', None, None, None, '2018-07-19 13:54:19', '2018-07-19 00:00:00', None, None, 'Nadanie', '000000', 'DB - MART_TABLE_ALL, MART_VIEW_ALL;', None, None, None, None),
                ('XXXXXXXXc', 'XXXXXXXXc', 'XXXXXXXXc', 'Pracownik', 'Biuro', '2018-07-19 12:38:19', None, None, None, '2018-07-19 13:55:54', '2018-07-19 00:00:00', None, None, 'Nadanie', '000000', 'DB - MART_TABLE_ALL;', None, None, None, None),
                ('XXXXXXXXd', 'XXXXXXXXd', 'XXXXXXXXd', 'Pracownik', 'Biuro', '2018-07-19 12:53:13', None, None, None, '2018-07-19 13:57:40', '2018-07-19 00:00:00', None, None, 'Nadanie', '000000', 'DB - MART_TABLE_ALL, MART_VIEW_ALL, MART_VIEW_XYZ;', None, None, None, None)
                ]

    dane = []
    with cx_Oracle.connect(login, password, tns, encoding="UTF-8") as con:
        with con.cursor() as cur:
            # after using function below, variable values are misplaced in executemany()
            cur.setinputsizes(None, 5000)

            for row in test_data:
                dane_rzedu = ()
                for v in row:
                    if v:
                        dane_rzedu += (str(v),)
                    else:
                        dane_rzedu += (v,)
                dane.append(dane_rzedu)
                if len(dane) % batch_size == 0:
                    cur.executemany(sql, dane)
                    dane = []
            if dane:
                cur.executemany(sql, dane)

        con.commit()

if __name__ == "__main__":
    write_data()

[cjbj]

Yay - a testcase! Thank you.

I can see column J doesn't look right - which looks like a potential trap for the unwary. You probably know that technically the setinputsizes() should/could be like something like:

            cur.setinputsizes(250, 250, 250, 250, 250, 250, 250, 250, 250, 250, 250, 250, 250, 250, 250, 250, 250, 250, 250, 250)

[tapurox]

Hi cjbj, thank You for answer and explanation of proper using setinputsizes() function. My mistake, sorry.