Problem binding value to TIMESTAMP column

[pavelbrziak]

1. What versions are you using?

platform.platform: Windows-10-10.0.19045-SP0 sys.maxsize > 2**32: True platform.python_version: 3.11.3 oracledb.version: 1.3.1

Oracle DB EE: 19.0.0.0.0

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

Error

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

When running insert into table using executemany, it seems that the values are not properly bound to timestamp field. Result error is: ORA-01400: cannot insert NULL into ("DB_USER"."TABLE_1"."COL_16")

Eventhough the provided dictionary contains value for COL_16, it seems that it wasn't bound.

It seems that it is a new issue, because in version oracledb.version: 1.2.2 it was inserted properly. However, I'm not able to replicate it currently for 1.2.2, since it is not possible in our environment to rollback to previous versions.

We tried to avoid this issue by using positional binding and using TO_TIMESTAMP, but the behaviour remained the same.

4. Does your application call init_oracle_client()?

No

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

Table creation:

CREATE TABLE DB_USER.TABLE_1
   (  
  COL_1 NUMBER(38,0) NOT NULL ENABLE, 
  COL_2 VARCHAR2(50 CHAR), 
  COL_3 VARCHAR2(100 CHAR), 
  COL_4 VARCHAR2(50 CHAR), 
  COL_5 VARCHAR2(100 CHAR), 
  COL_6 VARCHAR2(32 CHAR) NOT NULL ENABLE, 
  COL_7 VARCHAR2(500 CHAR), 
  COL_8 VARCHAR2(2000 CHAR), 
  COL_9 VARCHAR2(255 CHAR), 
  COL_10 VARCHAR2(500 CHAR), 
  COL_11 VARCHAR2(100 CHAR), 
  COL_12 VARCHAR2(200 CHAR), 
  COL_13 VARCHAR2(500 CHAR), 
  COL_14 VARCHAR2(100 CHAR) NOT NULL ENABLE, 
  COL_15 NUMBER(38,0) NOT NULL ENABLE, 
  COL_16 TIMESTAMP (6) NOT NULL ENABLE, 
  COL_17 NUMBER NOT NULL ENABLE
   );

Python code:

import oracledb
import sys
import platform

NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS'

def insert_into(ora_conn: oracledb.Connection):
    with ora_conn.cursor() as cursor:
        cursor.execute(f"alter session set NLS_TIMESTAMP_FORMAT = '{NLS_TIMESTAMP_FORMAT}'")
        sql = "insert into TABLE_1(COL_1, COL_2, COL_3, COl_4, COL_5, COL_6, COL_7, COL_8, COL_9, COL_10, COL_11, COL_12, COL_13, COL_14, COL_15, COL_16, COL_17) VALUES (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17)"
        data = []
        data.append({':1': '1002', ':2': '6546546sdf56454', ':3': 'TPS2_8sdf_212', ':4': '12', ':5': '12) Test TEXT', ':6': 'TEST Text 2', ':7': 'toto.domain.com', ':8': 'Test text - text', ':9': '12155FD4544UU', ':10': '77445522', ':11': "['OQK784AAfw']", ':12': 'tested', ':13': 'email.email@email.com', ':14': '', ':15': '', ':16': '2022-02-02 12:47:44', ':17': '1231564.784521'})
        print(f'Data process: {data}')
        cursor.executemany(sql, data, batcherrors=True)
        for error in cursor.getbatcherrors():
            print(error.message)
        ora_conn.commit()

dsn = """XXX"""

connection = oracledb.connect(user="DB_USER", password='XXX', dsn=dsn)

insert_into(connection)

Print result:

Data process: [{':1': '1002', ':2': '6546546sdf56454', ':3': 'TPS2_8sdf_212', ':4': '12', ':5': '12) Test TEXT', ':6': 'TEST Text 2', ':7': 'toto.domain.com', ':8': 'Test text - text', ':9': '12155FD4544UU', ':10': '77445522', ':11': "['OQK784AAfw']", ':12': 'tested', ':13': 'email.email@email.com', ':14': '', ':15': '', ':16': '2022-02-02 12:47:44', ':17': '1231564.784521'}]
ORA-01400: cannot insert NULL into ("DB_USER"."TABLE_1"."COL_16")

Thank you.

[anthony-tuininga]

I tried your example and after correcting a couple of issues (columns 14 and 15 require data but you were passing an empty string) the code worked as expected. Can you update your example to ensure it behaves as you expect and then supply the output when setting the environment variable PYO_DEBUG_PACKETS to any value before running your script? I only need the one that contains the insert statement.

[anthony-tuininga]

Closing - no further information provided