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

[nclee0320-git]

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().

[cjbj]

Out of interest, can you explain why you can't make your other tools strip whitespace from the start? Do you not use SQL formatters / linters?

[nclee0320-git]

Out of interest, can you explain why you can't make your other tools strip whitespace from the start? Do you not use SQL formatters / linters?

Why preserving the original SQL text matters

The purpose of this request is not to prevent SQL formatting or to require applications to use a particular SQL style.

The important requirement is that python-oracledb should provide a way for an application to execute exactly the SQL text that it supplied, without the driver silently modifying the statement.

This is particularly important for applications and database monitoring tools that use Oracle’s SQL_ID to correlate the SQL executed by the application with information stored in V$SQL, AWR, ASH, and execution-plan views.

Example use case

Suppose an application loads an existing SQL statement from a SQL file or from another database system and executes it without modifying or formatting it:

sql = """
SELECT
o.order_id,
o.customer_id,
o.order_date
FROM orders o
WHERE o.customer_id = :customer_id
AND o.order_date >= :start_date
"""

The application considers this exact string to be the SQL it is executing.

Later, a performance problem occurs and the developer wants to investigate it using Oracle’s performance views:

Application SQL
|
v
SQL_ID
|
+---- V$SQL
|
+---- V$SQL_PLAN_STATISTICS_ALL
|
+---- DBMS_XPLAN.DISPLAY_CURSOR
|
+---- AWR / ASH

The goal is to take the SQL that the application actually executed and correlate it with the corresponding Oracle cursor, execution statistics, and execution plan.

What happens if the driver strips whitespace?

If python-oracledb internally does:

statement = statement.strip()

before preparing the statement, the SQL sent to Oracle is no longer the exact string supplied by the application.

For example:

SQL supplied by the application:
"\n SELECT
o.order_id,
o.customer_id,
o.order_date
FROM orders o
WHERE o.customer_id = :customer_id
AND o.order_date >= :start_date\n"

becomes:

SQL actually prepared:
"SELECT
o.order_id,
o.customer_id,
o.order_date
FROM orders o
WHERE o.customer_id = :customer_id
AND o.order_date >= :start_date"

The SQL has the same semantics, but the SQL text is different.

This matters because Oracle’s SQL identification is based on the SQL text. Therefore, changing the text can result in a different SQL_ID.

For example:

SQL_ID calculated from the original SQL:
17bg4gj9xcnqj
SQL_ID for the SQL actually executed:
1gsfgsu17mvrr

The exact SQL text may therefore be different from the SQL text that the application believes it submitted.

Why this matters for execution-plan analysis

Consider a developer investigating a slow query.

They obtain the SQL from the application and calculate or otherwise identify its SQL_ID:

Application SQL_ID:
17bg4gj9xcnqj

They then try to find the corresponding cursor:

SELECT
sql_id,
child_number,
sql_fulltext
FROM v$sql
WHERE sql_id = '17bg4gj9xcnqj';

However, the actual cursor may have a different SQL_ID because the driver removed the leading and trailing whitespace before sending the statement to Oracle:

Application SQL
|
| SQL_ID = 17bg4gj9xcnqj
|
| X No exact match
|
v
python-oracledb
|
| statement.strip()
v
Modified SQL
|
| SQL_ID = 1gsfgsu17mvrr
|
v
Oracle
|
+---- V$SQL
+---- V$SQL_PLAN_STATISTICS_ALL
+---- DBMS_XPLAN.DISPLAY_CURSOR
+---- AWR / ASH

The developer may then incorrectly conclude that the SQL was not executed, or may have difficulty determining which Oracle cursor corresponds to the SQL they started with.

Comparing expected and actual execution plans

A common workflow is to compare an expected execution plan with the plan that was actually used in production.

For example:

SQL from the application
|
+---- EXPLAIN PLAN
| |
| +---- Expected execution plan
|
+---- Actual execution
|
+---- V$SQL
|
+---- DBMS_XPLAN.DISPLAY_CURSOR
|
+---- Actual execution plan

The developer expects both sides to refer to the same SQL statement.

If the application SQL has SQL_ID 17bg4gj9xcnqj, they may look for that SQL_ID in V$SQL and use it with DBMS_XPLAN.DISPLAY_CURSOR.

But if the driver has silently changed the SQL text, the actual cursor may instead have SQL_ID 1gsfgsu17mvrr.

This creates an unnecessary gap between:

1. the SQL the application loaded,
2. the SQL the application believes it executed,
3. the SQL recorded by Oracle,
4. the SQL_ID used to locate the cursor, and
5. the execution plan and runtime statistics associated with that cursor.

Again, this does not necessarily mean that removing leading or trailing whitespace changes the execution plan or the semantics of the SQL.

The problem is that it makes SQL identity and correlation less reliable.

This is especially relevant for tooling that needs to correlate an application SQL statement with:

• V$SQL
• V$SQL_PLAN_STATISTICS_ALL
• DBMS_XPLAN.DISPLAY_CURSOR
• AWR
• ASH
• SQL monitoring data
• the original SQL_ID
• child cursors and execution statistics

A concrete example

Suppose the application stores this SQL:

sql = "\n SELECT /* preserve_whitespace_test */ 1 FROM dual \n"

The application can calculate the SQL_ID for the exact string:

Original SQL:
"\n SELECT /* preserve_whitespace_test */ 1 FROM dual \n"
Original SQL_ID:
17bg4gj9xcnqj

If python-oracledb executes:

cursor.execute(sql)

but internally changes the statement to:

sql.strip()

then Oracle receives:

SELECT /* preserve_whitespace_test */ 1 FROM dual

and the corresponding SQL_ID can instead be:

Executed SQL_ID:
1gsfgsu17mvrr

The application has not intentionally modified the SQL, yet the SQL_ID observed in Oracle is different.

This can also be demonstrated by comparing the actual SQL text:

original_sql == executed_sql_text

False

original_sql.strip() == executed_sql_text

True

In our testing, the lengths were:

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

and the hash of original_sql.strip() matched the hash of the SQL text retrieved from V$SQL.

This demonstrates that the difference is caused by the driver’s whitespace normalization rather than by the application modifying or formatting the SQL.

What is being requested?

The request is not to remove the existing behavior unconditionally if doing so would be considered backward incompatible.

The important requirement is to provide a supported way for an application to preserve the exact SQL text supplied to Cursor.execute().

For example, an API could provide an option such as:

cursor.preserve_statement_whitespace = True

or:

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

The exact API design is of course up to the project.

The important behavior would be:

sql = "\n SELECT * FROM employees WHERE employee_id = :1 \n"
cursor.execute(sql)

should have an opt-in mode where the statement passed to the Oracle driver is exactly:

"\n SELECT * FROM employees WHERE employee_id = :1 \n"

rather than:

"SELECT * FROM employees WHERE employee_id = :1"

Whitespace validation does not require modifying the statement

If the purpose of strip() is only to detect an empty statement, the original string does not need to be replaced.

Instead of:

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

the logic could conceptually be:

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

This preserves the existing empty-statement validation while avoiding modification of the SQL text.

Summary

The main issue is not SQL formatting.

The issue is preserving SQL identity.

Applications may intentionally keep the exact SQL text that they execute and later use that SQL to correlate application behavior with Oracle’s performance data.

When the driver silently removes leading and trailing whitespace:

Application SQL
|
| exact text
v
python-oracledb
|
| .strip()
v
Different SQL text
|
v
Oracle
|
+---- Different SQL_ID
|
+---- V$SQL
+---- Execution statistics
+---- Execution plan
+---- AWR / ASH

This makes it harder to reliably connect the SQL held by the application with the SQL cursor and execution-plan information recorded by Oracle.

Therefore, a supported option to execute the exact SQL text supplied by the application, without implicit whitespace normalization, would be valuable for SQL tracing, SQL replay, performance analysis, and execution-plan correlation.

[nclee0320-git]

Out of interest, can you explain why you can't make your other tools strip whitespace from the start? Do you not use SQL formatters / linters?

Why preserving the original SQL text matters

The purpose of this request is not to prevent SQL formatting or to require applications to use a particular SQL style.

The important requirement is that python-oracledb should provide a way for an application to execute exactly the SQL text that it supplied, without the driver silently modifying the statement.

This is particularly important for applications and database monitoring tools that use Oracle’s SQL_ID to correlate the SQL executed by the application with information stored in V$SQL, AWR, ASH, and execution-plan views.

Example use case

Suppose an application loads an existing SQL statement from a SQL file or from another database system and executes it without modifying or formatting it:

sql = """
SELECT
o.order_id,
o.customer_id,
o.order_date
FROM orders o
WHERE o.customer_id = :customer_id
AND o.order_date >= :start_date
"""

The application considers this exact string to be the SQL it is executing.

Later, a performance problem occurs and the developer wants to investigate it using Oracle’s performance views:

Application SQL
|
v
SQL_ID
|
+---- V$SQL
|
+---- V$SQL_PLAN_STATISTICS_ALL
|
+---- DBMS_XPLAN.DISPLAY_CURSOR
|
+---- AWR / ASH

The goal is to take the SQL that the application actually executed and correlate it with the corresponding Oracle cursor, execution statistics, and execution plan.

What happens if the driver strips whitespace?

If python-oracledb internally does:

statement = statement.strip()

before preparing the statement, the SQL sent to Oracle is no longer the exact string supplied by the application.

For example:

SQL supplied by the application:
"\n SELECT
o.order_id,
o.customer_id,
o.order_date
FROM orders o
WHERE o.customer_id = :customer_id
AND o.order_date >= :start_date\n"

becomes:

SQL actually prepared:
"SELECT
o.order_id,
o.customer_id,
o.order_date
FROM orders o
WHERE o.customer_id = :customer_id
AND o.order_date >= :start_date"

The SQL has the same semantics, but the SQL text is different.

This matters because Oracle’s SQL identification is based on the SQL text. Therefore, changing the text can result in a different SQL_ID.

For example:

SQL_ID calculated from the original SQL:
17bg4gj9xcnqj
SQL_ID for the SQL actually executed:
1gsfgsu17mvrr

The exact SQL text may therefore be different from the SQL text that the application believes it submitted.

Why this matters for execution-plan analysis

Consider a developer investigating a slow query.

They obtain the SQL from the application and calculate or otherwise identify its SQL_ID:

Application SQL_ID:
17bg4gj9xcnqj

They then try to find the corresponding cursor:

SELECT
sql_id,
child_number,
sql_fulltext
FROM v$sql
WHERE sql_id = '17bg4gj9xcnqj';

However, the actual cursor may have a different SQL_ID because the driver removed the leading and trailing whitespace before sending the statement to Oracle:

Application SQL
|
| SQL_ID = 17bg4gj9xcnqj
|
| X No exact match
|
v
python-oracledb
|
| statement.strip()
v
Modified SQL
|
| SQL_ID = 1gsfgsu17mvrr
|
v
Oracle
|
+---- V$SQL
+---- V$SQL_PLAN_STATISTICS_ALL
+---- DBMS_XPLAN.DISPLAY_CURSOR
+---- AWR / ASH

The developer may then incorrectly conclude that the SQL was not executed, or may have difficulty determining which Oracle cursor corresponds to the SQL they started with.

Comparing expected and actual execution plans

A common workflow is to compare an expected execution plan with the plan that was actually used in production.

For example:

SQL from the application
|
+---- EXPLAIN PLAN
| |
| +---- Expected execution plan
|
+---- Actual execution
|
+---- V$SQL
|
+---- DBMS_XPLAN.DISPLAY_CURSOR
|
+---- Actual execution plan

The developer expects both sides to refer to the same SQL statement.

If the application SQL has SQL_ID 17bg4gj9xcnqj, they may look for that SQL_ID in V$SQL and use it with DBMS_XPLAN.DISPLAY_CURSOR.

But if the driver has silently changed the SQL text, the actual cursor may instead have SQL_ID 1gsfgsu17mvrr.

This creates an unnecessary gap between:

1. the SQL the application loaded,
2. the SQL the application believes it executed,
3. the SQL recorded by Oracle,
4. the SQL_ID used to locate the cursor, and
5. the execution plan and runtime statistics associated with that cursor.

Again, this does not necessarily mean that removing leading or trailing whitespace changes the execution plan or the semantics of the SQL.

The problem is that it makes SQL identity and correlation less reliable.

This is especially relevant for tooling that needs to correlate an application SQL statement with:

• V$SQL
• V$SQL_PLAN_STATISTICS_ALL
• DBMS_XPLAN.DISPLAY_CURSOR
• AWR
• ASH
• SQL monitoring data
• the original SQL_ID
• child cursors and execution statistics

A concrete example

Suppose the application stores this SQL:

sql = "\n SELECT /* preserve_whitespace_test */ 1 FROM dual \n"

The application can calculate the SQL_ID for the exact string:

Original SQL:
"\n SELECT /* preserve_whitespace_test */ 1 FROM dual \n"
Original SQL_ID:
17bg4gj9xcnqj

If python-oracledb executes:

cursor.execute(sql)

but internally changes the statement to:

sql.strip()

then Oracle receives:

SELECT /* preserve_whitespace_test */ 1 FROM dual

and the corresponding SQL_ID can instead be:

Executed SQL_ID:
1gsfgsu17mvrr

The application has not intentionally modified the SQL, yet the SQL_ID observed in Oracle is different.

This can also be demonstrated by comparing the actual SQL text:

original_sql == executed_sql_text

False

original_sql.strip() == executed_sql_text

True

In our testing, the lengths were:

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

and the hash of original_sql.strip() matched the hash of the SQL text retrieved from V$SQL.

This demonstrates that the difference is caused by the driver’s whitespace normalization rather than by the application modifying or formatting the SQL.

What is being requested?

The request is not to remove the existing behavior unconditionally if doing so would be considered backward incompatible.

The important requirement is to provide a supported way for an application to preserve the exact SQL text supplied to Cursor.execute().

For example, an API could provide an option such as:

cursor.preserve_statement_whitespace = True

or:

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

The exact API design is of course up to the project.

The important behavior would be:

sql = "\n SELECT * FROM employees WHERE employee_id = :1 \n"
cursor.execute(sql)

should have an opt-in mode where the statement passed to the Oracle driver is exactly:

"\n SELECT * FROM employees WHERE employee_id = :1 \n"

rather than:

"SELECT * FROM employees WHERE employee_id = :1"

Whitespace validation does not require modifying the statement

If the purpose of strip() is only to detect an empty statement, the original string does not need to be replaced.

Instead of:

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

the logic could conceptually be:

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

This preserves the existing empty-statement validation while avoiding modification of the SQL text.

Summary

The main issue is not SQL formatting.

The issue is preserving SQL identity.

Applications may intentionally keep the exact SQL text that they execute and later use that SQL to correlate application behavior with Oracle’s performance data.

When the driver silently removes leading and trailing whitespace:

Application SQL
|
| exact text
v
python-oracledb
|
| .strip()
v
Different SQL text
|
v
Oracle
|
+---- Different SQL_ID
|
+---- V$SQL
+---- Execution statistics
+---- Execution plan
+---- AWR / ASH

This makes it harder to reliably connect the SQL held by the application with the SQL cursor and execution-plan information recorded by Oracle.

Therefore, a supported option to execute the exact SQL text supplied by the application, without implicit whitespace normalization, would be valuable for SQL tracing, SQL replay, performance analysis, and execution-plan correlation.

[cjbj]

The pros & cons of stripping whitespace were known when the driver was designed. My question is what is blocking you from implementing best-practices, having coding standards, and using tooling to ensure your SQL statements are already stripped of whitespace. Please limit answers to non AI generated text.