Hacktoberfest 2026: le issue che i maintainer hanno segnato per ottobre, aperte e adatte ai principianti. Sfoglia le issue Hacktoberfest

Allow Cursor.execute() to preserve leading and trailing SQL whitespace to avoid SQL_ID changes

Aperta
  • Qualcosa non va?
#611 1 commento 0 reazioni 0 assegnatari Vedi su GitHub

Nessuno ha ancora preso questa issue.

Valutazione

Difficoltà
4/5
Tempo stimato
3-5 giorni
Idoneità per principianti
55/100
Tipo di issue
Bug
Chiarezza
Abbastanza chiara
Stato di attività
Attiva
Stack tecnologico
python
Ambito
database

Direzione di ricerca

Start by locating the statement normalization path described in the issue, where statement.strip() is called before the driver implementation receives SQL. Read the documentation on SQL ID calculation and review tests covering statement normalization and Cursor.execute(). Done means callers can use a supported API to preserve leading and trailing SQL whitespace while empty statements remain validated; the issue leaves open whether this should be the default or an opt-in.

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

Descrizione

enhancement

1. Describe your new request in detail

python-oracledb currently strips leading and trailing whitespace from every SQL statement before preparing it.

The current behavior is implemented conceptually as:

def _normalize_statement(self, statement):
    if statement is not None:
        statement = statement.strip()
        if not statement:
            raise ...
    return statement

This silently changes the exact SQL text supplied by the application.

This matters because an Oracle SQL_ID is sensitive to the exact SQL text, including whitespace. The python-oracledb documentation also notes that SQL ID calculation requires the same SQL text, including whitespace:

https://python-oracledb.readthedocs.io/en/v3.4.0/user_guide/bind.html#reducing-the-sql-version-count

Use case

Our application loads an existing SQL statement by SQL_ID from AWR or V$SQL, executes it without editing or formatting it, and then correlates the execution with:

  • V$SQL
  • V$SQL_PLAN_STATISTICS_ALL
  • DBMS_XPLAN.DISPLAY_CURSOR
  • AWR/ASH data
  • the original SQL ID and child cursor

The source SQL contained a leading newline and spaces, as well as trailing spaces.

Original length: 1038 characters
Executed V$SQL.SQL_FULLTEXT length: 1033 characters

After execution, we confirmed:

original_sql == executed_sql_text
# False

original_sql.strip() == executed_sql_text
# True

The SHA-256 hash of original_sql.strip() was also identical to the hash of the executed cursor's V$SQL.SQL_FULLTEXT.

As a result, the SQL ID changed even though the user did not edit or format the SQL:

Loaded SQL ID:   17bg4gj9xcnqj
Executed SQL ID: 1gsfgsu17mvrr

This creates problems for SQL replay, SQL tuning, execution plan collection, monitoring, and tools that need to correlate an execution with an existing SQL ID.

Minimal reproduction
import oracledb

connection = oracledb.connect(
    user="USER",
    password="PASSWORD",
    dsn="HOST/SERVICE_NAME",
)

sql = "\n  select /* preserve_whitespace_test */ 1 from dual   \n"

with connection.cursor() as cursor:
    cursor.execute(
        "select dbms_sql_translator.sql_id(:sql_text) from dual",
        sql_text=sql,
    )
    original_sql_id = cursor.fetchone()[0]

    cursor.execute(
        "select dbms_sql_translator.sql_id(:sql_text) from dual",
        sql_text=sql.strip(),
    )
    stripped_sql_id = cursor.fetchone()[0]

    cursor.execute(sql)
    prepared_sql = cursor.statement

    print("Submitted SQL:", repr(sql))
    print("Prepared SQL: ", repr(prepared_sql))
    print("Original SQL ID:", original_sql_id)
    print("Stripped SQL ID:", stripped_sql_id)
    print("Original preserved:", prepared_sql == sql)
    print("Stripped instead:  ", prepared_sql == sql.strip())

The prepared statement is the stripped version, and the SQL ID corresponds to the stripped SQL rather than the exact string supplied to Cursor.execute().

If the user has access to V$SESSION, the executed SQL ID can also be verified after executing the test SQL:

with connection.cursor() as cursor:
    cursor.execute(sql)

    cursor.execute("""
        select prev_sql_id
        from v$session
        where sid = sys_context('USERENV', 'SID')
    """)

    executed_sql_id = cursor.fetchone()[0]
    print("Executed SQL ID:", executed_sql_id)
Expected behavior

Ideally, Cursor.execute() should preserve the exact SQL statement supplied by the application.

Whitespace can still be used to validate whether a statement is empty without replacing the original statement:

if statement is not None:
    if not statement.strip():
        raise ...
return statement

If changing the default behavior is considered backward incompatible, please provide a supported opt-out option at the connection or cursor level, for example:

cursor.preserve_statement_whitespace = True

or:

cursor.execute(
    sql,
    parameters,
    preserve_statement_whitespace=True,
)

The important requirement is to have a supported public API that allows an application to submit the exact SQL text without silently removing leading or trailing whitespace.

2. Supporting information about tools and operating systems

Observed environment:

python-oracledb: 3.4.2
Oracle Database: 12.2.0.1.0
Python: [fill in the production server Python version]
Operating system: [fill in the production server OS and version]
python-oracledb mode: [Thin or Thick]

The installed python-oracledb 3.4.2 source was also inspected, and the normalization path calls statement.strip() before passing the statement to the driver implementation.

No SQL formatting, SQL editing, or explicit trimming was performed by the application before calling Cursor.execute().

Lingua principale
Python
Stelle
451
Fork
118
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.