Hang in Thin-mode using PL/SQL without setinputsizes()

[M4X1K02]

1. What versions are you using?

platform.platform: Windows-10-10.0.19044-SP0
sys.maxsize > 2**32: True
platform.python_version: 3.10.9
oracledb.version: 1.3.0b1
database version: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production, Version 19.3.0.0.0

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

In Thin-mode it is a hang.

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

The program below hangs in Thin-mode , while it passes in Thick-mode. This seems to be linked to setinputsizes, since changing the order in which the items are updated either hangs the program or passes. I assume the input size for the attributes is set by the first batch, so if the second batch exceeds the size, it hangs and unfortunately does not give back an error. I also tried this with regular SQL, which worked just fine.

4. Does your application call init_oracle_client()?

No.

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

Python example:

    import datetime
    from typing import List

    # oracledb.init_oracle_client()
    conn = oracledb.connect(<TO BE FILLED>)
    cur = conn.cursor()

    def chunked(iterable, n) -> List[List]:
        """Breaks an iterable into chunks of equal size."""
        temp = [[] for _ in range(n)]
        for ix, item in enumerate(iterable):
            temp[ix % n].append(item)
        return temp

    plSql = """
    BEGIN
        update_row(:timestamp, :attribute1, :id);
    END;"""

    sql = """
    UPDATE TEST_TABLE t1 
        SET 
            t1.timestamp= :timestamp, 
            t1.attribute1= :attribute1 
        WHERE 
            t1.id=:id"""

    plSqlParam = [
        {"timestamp": datetime.datetime.today(), "attribute1": "FIXED", "id": 1},
        {"timestamp": datetime.datetime.today(), "attribute1": "FIXED", "id": 2},
        {"timestamp": datetime.datetime.today(), "attribute1": "FIXED", "id": 3},
        {"timestamp": datetime.datetime.today(), "attribute1": "NEGOTIABLE", "id": 4},
    ]

    plSqlParamChunked = chunked(plSqlParam, 2)

    #cur.setinputsizes(attribute1=10)
    for plSqlParamChunk in plSqlParamChunked:
        cur.executemany(plSql, plSqlParamChunk)
        # cur.executemany(sql, plSqlParamChunk)
        conn.commit()

SQL:

create or replace PROCEDURE update_row (
    timestamp_p IN TIMESTAMP,
    attribute1_p IN VARCHAR2,
    id_p in NUMBER
)
IS
BEGIN
    UPDATE "TEST_TABLE" t1
    SET
        t1."TIMESTAMP" = timestamp_p,
        t1.attribute1 = attribute1_p
    WHERE t1."ID" = id_p;
END update_row;
/

CREATE TABLE TEST_TABLE(
  "TIMESTAMP" timestamp,
  attribute1 varchar2(10),
  "ID" NUMBER
);
/

INSERT INTO "TEST_TABLE"("TIMESTAMP", attribute1, "ID") VALUES(SYSDATE, 'a', 1);
INSERT INTO "TEST_TABLE"("TIMESTAMP", attribute1, "ID") VALUES(SYSDATE, 'b', 2);
INSERT INTO "TEST_TABLE"("TIMESTAMP", attribute1, "ID") VALUES(SYSDATE, 'c', 3);
INSERT INTO "TEST_TABLE"("TIMESTAMP", attribute1, "ID") VALUES(SYSDATE, 'd', 4);
/

drop table "TEST_TABLE";
/

[anthony-tuininga]

I can replicate the problem and will look into why it is occurring. Thanks for the report!

[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]

It works now, thank you very much!