Hang in Thin-mode using PL/SQL without setinputsizes() #132
Description
- 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
- Is it an error or a hang or a crash?
In Thin-mode it is a hang.
- 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.
- Does your application call init_oracle_client()?
No.
- 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";
/