executemany() with /*+ append_values */ and strings over 32767 bytes stores another row's value or raises ORA-03106 (Thin)

Offen
  • Stimmt etwas nicht?
#633 0 Kommentare 0 Reaktionen 0 zugewiesene Personen Auf GitHub ansehen

Dieses Issue hat noch niemand übernommen.

Bewertung

Schwierigkeit
4/5
Geschätzter Aufwand
3-5 Tage
Anfängerfreundlichkeit
55/100
Issue-Typ
Bug
Klarheit
Klar beschrieben
Aktivitätsstatus
Aktiv
Tech-Stack
python
Bereich
backend, database

Rechercherichtung

Start with the runnable script in the issue, which isolates the failure to Thin mode executemany() with the append_values hint and LONG-bound strings over 32767 bytes. Compare those results with conventional inserts and hinted batches of shorter strings, then trace the Thin mode array-bind and direct-path handling. Done means the script stores each row’s correct value or returns a clear error instead of corrupting data or raising ORA-03106.

Vom Indexierungsmodell aus dem Issue-Text verfasst.

Beschreibung

  1. What versions are you using?
database: 23.26.1.0.0 (Oracle Database 23ai Enterprise Edition, container-registry.oracle.com/database/enterprise)
platform.platform: Linux-6.18.54.1-microsoft-standard-WSL2-x86_64-with-glibc2.41
sys.maxsize > 2**32: True
platform.python_version: 3.14.10
oracledb.__version__: 26.1.0

We first saw it on Oracle Database 19.17 (RAC on Exadata) with the Rust driver (rust-oracledb 26.0.0-beta.5),
which shares the Thin protocol implementation: same ORA-03106 there.

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

Both an error and silent wrong data.

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

executemany() of INSERT /*+ append_values */ ... VALUES (:1, :2) into a CLOB column, when a batch contains a
string longer than 32767 bytes (bound as LONG):

  • if other rows in the batch are short, there is no error, but the long row gets another row's value (the
    first row of the batch is repeated);
  • if several rows are long, ORA-03106: fatal two-task communication protocol error.

The same batches without the hint (conventional insert), and batches whose values are all <= 32767 bytes with the
hint, are inserted correctly. Output of the script below (expected: row N has the letter of row N and its size):

database: 23.26.1.0.0
OK  [100, 5000]            conventional   1:100x'a' 2:5000x'b'
OK  [100, 5000]            append_values  1:100x'a' 2:5000x'b'
OK  [100, 40000]           conventional   1:100x'a' 2:40000x'b'
BAD [100, 40000]           append_values  1:100x'a' 2:100x'a'
OK  [40000, 100]           conventional   1:40000x'a' 2:100x'b'
BAD [40000, 100]           append_values  1:40000x'a' 2:40000x'a'
OK  [40000, 40000, 40000]  conventional   1:40000x'a' 2:40000x'b' 3:40000x'c'
BAD [40000, 40000, 40000]  append_values  ORA-03106: fatal two-task communication protocol error

I have not tested Thick mode (no Instant Client available here), so I don't know whether this is a Thin mode
issue or a database restriction on direct-path inserts with LONG binds. Even if direct path cannot take LONG binds,
I would expect an error rather than another row's value being stored.

  1. Does your application call init_oracle_client()?

No (Thin mode).

  1. Include a runnable Python script that shows the problem.
import os
import oracledb

TABLE = "append_values_long_test"


def connect():
    return oracledb.connect(
        user=os.environ["PYO_USER"],
        password=os.environ["PYO_PASSWORD"],
        dsn=os.environ["PYO_DSN"],
    )


with connect() as conn:
    print("database:", conn.version)
    cursor = conn.cursor()
    cursor.execute(f"drop table if exists {TABLE} purge")
    cursor.execute(f"create table {TABLE} (id number, c clob)")

cases = [
    [100, 5000],             # all values <= 32767 bytes
    [100, 40000],            # second value > 32767 bytes
    [40000, 100],
    [40000, 40000, 40000],
]
for sizes in cases:
    for hint in ("", "/*+ append_values */ "):
        # new connection for each case, as ORA-03106 may leave it unusable
        with connect() as conn:
            cursor = conn.cursor()
            cursor.execute(f"truncate table {TABLE}")
            rows = [(i + 1, "abcdefghij"[i] * size) for i, size in enumerate(sizes)]
            try:
                cursor.executemany(
                    f"insert {hint}into {TABLE} (id, c) values (:1, :2)", rows
                )
                conn.commit()
                cursor.execute(
                    f"select id, dbms_lob.getlength(c), substr(c, 1, 1) "
                    f"from {TABLE} order by id"
                )
                result = " ".join(f"{i}:{n}x'{ch}'" for i, n, ch in cursor)
            except oracledb.Error as e:
                result = str(e).splitlines()[0]
            expected = " ".join(
                f"{i + 1}:{size}x'{'abcdefghij'[i]}'" for i, size in enumerate(sizes)
            )
            label = "append_values" if hint else "conventional"
            status = "OK " if result == expected else "BAD"
            print(f"{status} {str(sizes):<22} {label:<14} {result}")

with connect() as conn:
    conn.cursor().execute(f"drop table {TABLE} purge")
Vorherrschende Sprache
Python
Sterne
458
Forks
123
Keine gemergten PRs in 30 T.

Entwicklungsumgebung

Erste Schritte

  1. Lesen Sie das ganze Issue und danach den Beitragsleitfaden des Projekts.
  2. Schreiben Sie ins Issue, dass Sie es übernehmen — das erspart doppelte Arbeit.
  3. Forken Sie das Repository und arbeiten Sie in einem Branch.
  4. Öffnen Sie einen Pull Request, der die Issue-Nummer nennt.

Mehr aus oracle/python-oracledb

Alle Issues in oracle/python-oracledb

Ähnliche Issues

Weitere Issues zu Python

Neue Issues direkt in Ihr Postfach

Eine kurze Übersicht über anfängerfreundliche GitHub-Issues.