Thin mode: an outputtypehandler redefining a CLOB as DB_TYPE_LONG misreads the reply when the statement is cached

Aperta
  • Qualcosa non va?
#634 0 commenti 0 reazioni 0 assegnatari Vedi su GitHub

Nessuno ha ancora preso questa issue.

  • #635 di @lemenkov — chiusa senza merge

Valutazione

Difficoltà
4/5
Tempo stimato
3-5 giorni
Idoneità per principianti
35/100
Tipo di issue
Bug
Chiarezza
Specificata chiaramente
Stato di attività
Ferma
Stack tecnologico
python
Ambito
backend-api-design, database

Direzione di ricerca

Start with MessageWithData._preprocess_query() in src/oracledb/impl/thin/messages/base.pyx and compare its output type handler change handling with _process_error_info() for ORA-00932. The report says a linked pull request contains a fix, so review that change and the related cursor and LOB tests: tests/cursor, tests/data_types/test_1500_lobs.py, test_1600_longs.py, and test_2500_lobs_async.py. Done means cached statements return the expected CLOB or LONG values across the reported query orders and pass those tests.

Scritto dal modello di indicizzazione a partire dal testo della issue.

Descrizione

Version: python-oracledb 26.1.0, and also the current main (26.2.0b1, commit 27132e11, 2026-11-12), thin mode, Python 3.14, Linux.

Database: Oracle Database 23ai Free (23.26), 21c XE and 18c XE. It reproduces the same way on all three.

What happens

A query runs once without a type handler. The same SQL then runs on a new cursor whose outputtypehandler returns cursor.var(oracledb.DB_TYPE_LONG, ...) for the CLOB column. That second fetch misreads the reply, and the error depends on the columns around the CLOB:

query (plain first, then with the handler) default statement cache stmtcachesize=0
SELECT TO_CLOB('lob text') FROM dual DPY-5000: internal error: unknown protocol message type 40 ok
SELECT TO_CLOB('lob text'), DATE '2026-11-04' FROM dual ValueError: year must be in 1..9999, not -10062 ok
SELECT DATE '2026-11-04', TO_CLOB('lob text') FROM dual DPY-5000 ok
SELECT TO_CLOB('lob text'), 1.5 FROM dual DPY-5015: encoded number data too long (40 bytes) ok
SELECT TO_CLOB('lob text'), 'abc' FROM dual UnicodeDecodeError ok

If the handler is there on the first execute of the SQL, or the statement cache is off, every case returns the expected row.

Reproducer

import oracledb

DSN = "host:1521/service"  # any 12c+ database; queries DUAL only


def clob_as_long(cursor, metadata):
    if metadata.type_code is oracledb.DB_TYPE_CLOB:
        return cursor.var(oracledb.DB_TYPE_LONG, arraysize=cursor.arraysize)
    return None


sql = "SELECT TO_CLOB('lob text') FROM dual"
for cache in (20, 0):
    conn = oracledb.connect(user="...", password="...", dsn=DSN, stmtcachesize=cache)
    conn.cursor().execute(sql).fetchone()          # 1. plain: LOB locator
    cur = conn.cursor()
    cur.outputtypehandler = clob_as_long           # 2. same SQL, now as LONG
    try:
        cur.execute(sql)
        print(cache, "ok", cur.fetchone())
    except Exception as exc:
        print(cache, "fails:", type(exc).__name__, exc)
    conn.close()

Output:

20 fails: InternalError DPY-5000: internal error: unknown protocol message type 40 ...
0 ok ('lob text',)

Expected

('lob text',) in both cases, as with the cache off.

The reverse order is wrong too, silently: run the SQL first with the CLOB→LONG handler, then on a new cursor without a handler (or with a handler that leaves the CLOB alone), and the CLOB column comes back as str instead of a LOB.

Cause

In MessageWithData._preprocess_query() (src/oracledb/impl/thin/messages/base.pyx), when the output type handler differs from the one used for the statement's last fetch, the fetch variables are rebuilt for the new handler, but _requires_define isn't set. The statement is cached, so no new describe arrives either, and the database keeps returning the LOB columns in the form the last define asked for. The client then decodes them in the form the new fetch variables expect.

Setting _requires_define there isn't enough. On a cached cursor it sends a define without an execute, and the fetch fails with ORA-01002. The fix in the attached PR clears the cursor instead, as _process_error_info() already does for ORA-00932, but only when the statement has LOB columns (_no_prefetch). The statement is then parsed, described and defined again, and the fetch variables come from the new describe. With the fix, all 15 combinations (3 orders × 5 queries) pass on 23ai, 21c and 18c, and tests/cursor, tests/data_types/test_1500_lobs.py, test_1600_longs.py and test_2500_lobs_async.py pass against 23ai (597 passed, 8 skipped).

Lingua principale
Python
Stelle
458
Fork
123
Nessuna PR unita negli ultimi 30g

Preparare l'ambiente

Come iniziare

  1. Leggi tutta la issue e poi la guida ai contributi del progetto.
  2. Commenta sulla issue per dire che te ne occupi tu — evita che due persone facciano lo stesso lavoro.
  3. Fai un fork del repository e lavora su un branch.
  4. Apri una pull request che faccia riferimento al numero della issue.

Altre issue di oracle/python-oracledb

Tutte le issue di oracle/python-oracledb

Issue simili

Altre issue su Python

Ricevi le nuove issue nella tua casella

Un breve riepilogo di issue GitHub adatte ai principianti.