oracle / python-oracledb

Python driver for Oracle Database conforming to the Python DB API 2.0 specification. This is the renamed, new major release of cx_Oracle
https://oracle.github.io/python-oracledb
Other
339 stars 67 forks source link

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

Closed M4X1K02 closed 1 year ago

M4X1K02 commented 1 year ago
  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

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

In Thin-mode it is a hang.

  1. 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.

  1. Does your application call init_oracle_client()?

No.

  1. 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 commented 1 year ago

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

anthony-tuininga commented 1 year ago

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 commented 1 year ago

It works now, thank you very much!