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

Ouverte
  • Quelque chose ne va pas ?
#633 0 commentaires 0 réactions 0 personnes assignées Voir sur GitHub

Personne n'a encore pris cette issue.

Évaluation

Difficulté
4/5
Temps estimé
3-5 jours
Accessibilité débutants
55/100
Type d'issue
Bug
Clarté
Clairement spécifiée
Activité
Active
Stack technique
python
Domaine
backend, database

Piste de recherche

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.

Rédigé par le modèle d'indexation à partir du texte de l'issue.

Description

  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")
Langage dominant
Python
Étoiles
458
Forks
123
Aucune PR mergée en 30 j

Préparer son environnement

Par où commencer

  1. Lisez l'issue en entier, puis le guide de contribution du projet.
  2. Signalez en commentaire que vous la prenez — cela évite que deux personnes fassent le même travail.
  3. Forkez le dépôt et travaillez sur une branche.
  4. Ouvrez une pull request qui référence le numéro de l'issue.

Autres issues de oracle/python-oracledb

Toutes les issues de oracle/python-oracledb

Issues similaires

Plus d'issues Python

Recevez les nouvelles issues par e-mail

Un résumé court des issues GitHub adaptées aux débutants.