OracleDB跨DBLink读取多CLOB列致Python崩溃，求SQLAlchemy解决方案

问题概述 #

使用SQLAlchemy搭配oracledb驱动开发应用时，进程在try-except异常块内意外终止，经排查问题源于用于转换CLOB、BLOB为字符串的output_type_handler函数。单独使用oracledb时可正常通过read()读取CLOB内容，但启用该handler后，即使调用fetchone()也会导致Jupyter崩溃（退出码3221225477）。设置oracledb.defaults.fetch_lobs = False无法解决问题，且该问题仅在通过DBLink访问包含多个CLOB列的表时触发，本地访问同表则正常。执行SELECT *时崩溃，单独查询某CLOB列或排除大内容CLOB列（如存储HTML的EMAIL_CONTENT）则正常。

问题中的output_type_handler代码如下：

def output_type_handler(cursor, metadata):
    if metadata.type_code is oracledb.DB_TYPE_CLOB:
        return cursor.var(oracledb.DB_TYPE_LONG, arraysize=cursor.arraysize)
    if metadata.type_code is oracledb.DB_TYPE_BLOB:
        return cursor.var(oracledb.DB_TYPE_LONG_RAW, arraysize=cursor.arraysize)
    if metadata.type_code is oracledb.DB_TYPE_NCLOB:
        return cursor.var(oracledb.DB_TYPE_LONG_NVARCHAR, arraysize=cursor.arraysize)

环境信息 #

• 数据库版本：19.6c
• 客户端版本：21c
• Python版本：3.9.5
• oracledb版本：1.4.2
• SQLAlchemy版本：2.0.23
• 连接模式：厚模式（Thick Mode）

涉及表结构 #

CREATE TABLE AUTOMAIL_JOB_LIST (
ID NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY START WITH 1 INCREMENT BY 1,
ID_DEFINITION NUMBER,
SERVICE_GROUP VARCHAR2 (100 Char),
SUBJECT VARCHAR2 (300 Char),
EMAIL_CONTENT CLOB,
ATTACHMENT CLOB,
SEND_TO CLOB,
SEND_CC CLOB,
SEND_BCC CLOB,
STATUS NUMBER(2) DEFAULT 0,
CREATE_DATE DATE DEFAULT SYSDATE,
SEND_DATE DATE DEFAULT NULL,
OWNER_EMAIL VARCHAR2(200 CHAR))

示例代码 #

SQLAlchemy环境代码 #

from sqlalchemy import create_engine
import pandas as pd

engine = create_engine("", connect_args = {
                                "encoding": "UTF-16",
                                "nencoding": "UTF-16"
                            },
                        thick_mode = True,
                        pool_pre_ping = True,
                        echo_pool = True,
                        pool_size = 1)

query = """
            SELECT *
            FROM AUTOMAIL_JOB_LIST@DBLINK
        """

df = pd.read_sql(query, engine)

纯OracleDB环境代码 #

import oracledb
import pandas as pd
oracledb.init_oracle_client()

def output_type_handler(cursor, metadata):
    if metadata.type_code is oracledb.DB_TYPE_CLOB:
        return cursor.var(oracledb.DB_TYPE_LONG, arraysize=cursor.arraysize)
    if metadata.type_code is oracledb.DB_TYPE_BLOB:
        return cursor.var(oracledb.DB_TYPE_LONG_RAW, arraysize=cursor.arraysize)
    if metadata.type_code is oracledb.DB_TYPE_NCLOB:
        return cursor.var(oracledb.DB_TYPE_LONG_NVARCHAR, arraysize=cursor.arraysize)

query = """
            SELECT *
            FROM AUTOMAIL_JOB_LIST@DBLINK
        """

oracle_connection_string = oracledb.makedsn(oracle_host, oracle_port, service_name = oracle_service_name)
oracle_connection = oracledb.connect(user = oracle_username, password = oracle_password, dsn = oracle_connection_string, encoding = "UTF-16", nencoding = "UTF-16")
oracle_connection.outputtypehandler = output_type_handler

df = pd.read_sql(query, oracle_connection)

# cursor = oracle_connection.cursor()
# cursor.execute(query)
# row = cursor.fetchone()

可行解决方案 #

1. 移除全局类型转换，手动处理CLOB对象 #

问题根源是DBLink场景下，oracledb的CLOB转LONG类型转换存在兼容性bug，尤其是厚模式多CLOB列场景。建议移除全局output_type_handler，改为查询后手动读取CLOB内容：

from sqlalchemy import create_engine
import pandas as pd
import oracledb

engine = create_engine("", 
                        connect_args={"encoding": "UTF-16", "nencoding": "UTF-16"},
                        thick_mode=True,
                        pool_pre_ping=True,
                        echo_pool=True,
                        pool_size=1)

query = """SELECT * FROM AUTOMAIL_JOB_LIST@DBLINK"""

with engine.connect() as conn:
    # 获取底层oracledb游标
    cursor = conn.connection.cursor()
    cursor.execute(query)
    # 获取列名
    columns = [col[0] for col in cursor.description]
    # 逐行处理CLOB
    processed_rows = []
    for row in cursor.fetchall():
        new_row = []
        for item in row:
            # 判断是否为LOB对象，是则读取内容
            if isinstance(item, oracledb.LOB):
                new_row.append(item.read())
            else:
                new_row.append(item)
        processed_rows.append(new_row)
    # 转换为DataFrame
    df = pd.DataFrame(processed_rows, columns=columns)

2. 调整output_type_handler逻辑，适配DBLink场景 #

若必须保留类型转换，可修改handler，仅在非DBLink查询时启用转换，DBLink查询使用默认处理：

def output_type_handler(cursor, metadata):
    # 通过SQL语句判断是否为DBLink查询
    if "@DBLINK" not in cursor.statement.upper():
        if metadata.type_code is oracledb.DB_TYPE_CLOB:
            return cursor.var(oracledb.DB_TYPE_LONG, arraysize=cursor.arraysize)
        if metadata.type_code is oracledb.DB_TYPE_BLOB:
            return cursor.var(oracledb.DB_TYPE_LONG_RAW, arraysize=cursor.arraysize)
        if metadata.type_code is oracledb.DB_TYPE_NCLOB:
            return cursor.var(oracledb.DB_TYPE_LONG_NVARCHAR, arraysize=cursor.arraysize)
    # DBLink场景返回None，使用默认处理逻辑
    return None

# 在SQLAlchemy中为所有连接绑定handler
from sqlalchemy import create_engine, event

engine = create_engine("", 
                        connect_args={"encoding": "UTF-16", "nencoding": "UTF-16"},
                        thick_mode=True,
                        pool_pre_ping=True,
                        echo_pool=True,
                        pool_size=1)

# 监听连接创建事件，设置handler
@event.listens_for(engine, "connect")
def on_connect(dbapi_connection, connection_record):
    dbapi_connection.outputtypehandler = output_type_handler

3. 升级依赖版本 #

尝试升级oracledb到最新稳定版（如2.x系列），新版本可能修复了DBLink下的类型转换兼容性问题；同时升级SQLAlchemy到最新版本，确保与oracledb的适配性。

内容的提问来源于stack exchange，提问作者Tuan