Using "batcherrors=True" raises a ORA-03137 exception

[M4X1K02]

1. What versions are you using?

oracle database: 19c platform.platform: Windows-10-10.0.19044-SP0 sys.maxsize > 2**32: True platform.python_version: 3.10.9 oracledb.version: 1.2.1

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

I'm not sure, I'd say it is an error and a hang. For details see 3.

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

cur.executemany() raises the following error if batcherrors=True. But with batcherrors=False or even catching the exception and doing nothing with it, the query completes and data is inserted. I also have found another issue, where cur.executemany() simply hangs forever with batcherrors=False but is also showing the same errormessage when turning batcherrors back on. Unfortunately I have not been able to completely reproduce this error in a smaller factor, which resulted in this example (5.), but I still believe they are related. The only difference between this example and the other one (which is not shown here because it is simply to big) is that with batcherrors=False this example (5.) completes, while the other one hangs.

_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
..\mobile-env\lib\site-packages\oracledb\cursor.py:439: in executemany
    self._impl.executemany(self, num_execs, bool(batcherrors),
src\oracledb\impl/thin/cursor.pyx:158: in oracledb.thin_impl.ThinCursorImpl.executemany
    ???
src\oracledb\impl/thin/protocol.pyx:383: in oracledb.thin_impl.Protocol._process_single_message
    ???
src\oracledb\impl/thin/protocol.pyx:384: in oracledb.thin_impl.Protocol._process_single_message
    ???
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

>   ???
E   oracledb.exceptions.DatabaseError: ORA-03137: TTC-Paket in falschem Format abgelehnt: [kpoal8Check-3] [32768] [0] [0x000000000] [527656] [] [] []

src\oracledb\impl/thin/protocol.pyx:377: DatabaseError

4. Does your application call init_oracle_client()?

No. (Thin mode)

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

import oracledb

# replace with your connection
conn = oracledb.connect(<TO_BE_FILLED>)

features = ["Sirene"]
typeObj = conn.gettype("FEATURES_TYPE")
if features:
    featureObj = typeObj.newobject(features)
else:
    featureObj = None

cur = conn.cursor()
# declaring object types as stated in discussion #127 
cur.setinputsizes(features=typeObj, timestamp=oracledb.DB_TYPE_TIMESTAMP)
sql_params = [{"features": featureObj, "timestamp": datetime.datetime.today()}]
cur.executemany("BEGIN\nadd_car_test(:features,:timestamp);\nEND;", sql_params, batcherrors=True)

SQL table:

CREATE TABLE "CARS_TEST" (
  "ID" NUMBER GENERATED ALWAYS AS IDENTITY, 
  "FEATURES" "FEATURES_TYPE",
  "TIMESTAMP" timestamp
) NESTED TABLE "FEATURES" STORE AS "FEATURES_TEST_TAB";

SQL procedure:

create or replace PROCEDURE add_car_test (
        features_p IN features_type,
        timestamp_p in timestamp
)
IS
BEGIN
    insert into CARS_TEST(FEATURES, "TIMESTAMP") VALUES(features_p, timestamp_p);
END add_car_test;

[cjbj]

Thanks for the report and testcase.

[anthony-tuininga]

The parameter batcherrors can only be used with insert, update, delete and merge statements, not with PL/SQL. This is not being checked in thin mode (so you are seeing the ORA-3137 error instead). I'll get that corrected. In the meantime, remove the batcherrors parameter as it cannot be used with PL/SQL!

[anthony-tuininga]

I have pushed a patch that should correct this issue and added a relevant test case. If you are able to build from source you can verify that it corrects your issue as well.

[M4X1K02]

Built from source and now prints the error message

oracledb.exceptions.ProgrammingError: DPY-2040: parameters "batcherrors" and "arraydmlrowcounts" may only be true when used with insert, update, delete and merge statements

Thank you and closing this issue.