Oracle 超长字符串更新问题ORA-01704: string literal too long

原创

高老师

修改于 2026-02-09 11:50:38

1740

文章被收录于专栏：编程语言的世界编程语言的世界

Oracle 超长字符串更新问题解决方案

问题背景

在使用 Navicat 对 Oracle 数据库执行 UPDATE 操作时，当需要更新的字段包含超长的 JSON 字符串时，会遇到以下错误：

ORA-01704: string literal too long

这是因为 Oracle 对字符串字面量有长度限制，具体限制如下：

VARCHAR2 字段：最大 4000 字节（单字节字符集）或 2000 字符（多字节字符集）
SQL 语句中的字符串字面量：最大 4000 字节

问题场景

典型的失败案例

UPDATE X6_SAP_GLS.X2_MSSAP_LOG t
SET t.JSON = '{"billsType":"DivsionTradeRecoBills","billsTypeEnumName":"BusinessDivsionTradeRecoBills",...[超长JSON数据]...}'
WHERE t.BILLS_ID = 'DZ260112-68LA-000001';

当 JSON 数据超过 4000 字节时，Navicat 执行会报错：

❌ ORA-01704: string literal too long

为什么 Navicat 无法处理

直接拼接 SQL：Navicat 生成的 SQL 语句是直接将字符串拼接到 SQL 中
字符串字面量限制：Oracle 限制了 SQL 语句中字符串字面量的最大长度为 4000 字节
无法自动转换为 CLOB：Navicat 不会自动将超长字符串转换为 CLOB 类型处理

解决方案

方案一：使用 Python + cx_Oracle（推荐）

通过 Python 的 cx_Oracle 库，使用参数化查询（绑定变量）来避免字符串长度限制。

优势

✅ 自动处理 CLOB 类型
✅ 防止 SQL 注入
✅ 支持超长字符串（最大 4GB）
✅ 批量执行效率高

完整代码实现

import cx_Oracle
import sys
import re

# 数据库配置
DB_CONFIG = {
    'user': 'X16_sap_gls',
    'password': 'your_password',
    'dsn': '192.178.110.217:15121/ORCL'
}

def extract_update_info(sql_line):
    """从 UPDATE 语句中提取表名、WHERE 条件和 JSON 数据"""
    pattern = r"UPDATE\s+([\w.]+)\s+(\w+)\s+SET\s+\w+\.JSON\s*=\s*'(.+?)'\s*(?:WHERE\s+(.+?))?$"

    match = re.search(pattern, sql_line, re.IGNORECASE | re.DOTALL)
    if match:
        return {
            'table': match.group(1),
            'alias': match.group(2),
            'json': match.group(3),
            'where': match.group(4) if match.group(4) else None
        }
    return None

def extract_update_statements(file_path):
    """从文件中提取所有有效的 UPDATE 语句"""
    update_statements = []

    with open(file_path, 'r', encoding='utf-8') as f:
        for line_num, line in enumerate(f, 1):
            line = line.strip()
            # 跳过空行和日志行
            if not line or line.startswith('[202') or '内检索到' in line:
                continue

            # 查找包含 UPDATE 的行
            if 'UPDATE' in line.upper():
                # 移除可能的时间戳前缀
                if ']' in line and '[' in line:
                    idx = line.rfind(']')
                    if idx > 0 and idx < 50:
                        line = line[idx+1:].strip()

                # 移除 schema 前缀（如 X6_SAP_GLS>）
                if '>' in line and line.index('>') < 20:
                    line = line[line.index('>')+1:].strip()

                if line.upper().startswith('UPDATE'):
                    update_statements.append((line_num, line))

    return update_statements

def execute_update_with_clob(cursor, table, alias, json_data, where_clause):
    """使用 CLOB 类型执行 UPDATE，避免字符串长度限制"""

    # 构建 SQL 语句，使用绑定变量
    if where_clause:
        sql = f"UPDATE {table} {alias} SET {alias}.JSON = :json_data WHERE {where_clause}"
    else:
        sql = f"UPDATE {table} {alias} SET {alias}.JSON = :json_data"

    # 执行 UPDATE，cx_Oracle 会自动将超长字符串转换为 CLOB
    cursor.execute(sql, json_data=json_data)
    return cursor.rowcount

def execute_sql_file(sql_file_path):
    """读取并执行 SQL 文件"""
    connection = None
    cursor = None

    try:
        # 连接数据库
        print(f"正在连接到 Oracle 数据库 {DB_CONFIG['dsn']}...")
        connection = cx_Oracle.connect(
            user=DB_CONFIG['user'],
            password=DB_CONFIG['password'],
            dsn=DB_CONFIG['dsn'],
            encoding="UTF-8"
        )
        print("数据库连接成功！\n")

        cursor = connection.cursor()

        # 提取 UPDATE 语句
        print(f"正在解析 SQL 文件: {sql_file_path}")
        update_statements = extract_update_statements(sql_file_path)

        total_statements = len(update_statements)
        print(f"共找到 {total_statements} 条有效的 UPDATE 语句\n")

        if total_statements == 0:
            print("警告: 没有找到有效的 UPDATE 语句！")
            return

        # 执行每条 SQL
        success_count = 0
        error_count = 0

        for idx, (line_num, sql) in enumerate(update_statements, 1):
            try:
                print(f"[{idx}/{total_statements}] (第{line_num}行) 执行中...", end=' ')

                # 解析 UPDATE 语句
                update_info = extract_update_info(sql)

                if update_info:
                    # 使用参数化查询执行
                    rows_affected = execute_update_with_clob(
                        cursor,
                        update_info['table'],
                        update_info['alias'],
                        update_info['json'],
                        update_info['where']
                    )
                    print(f"成功 (影响 {rows_affected} 行)")
                    success_count += 1
                else:
                    # 如果解析失败，尝试直接执行
                    cursor.execute(sql)
                    rows_affected = cursor.rowcount
                    print(f"成功 (影响 {rows_affected} 行)")
                    success_count += 1

            except cx_Oracle.DatabaseError as e:
                error, = e.args
                print(f"失败")
                print(f"  错误代码: {error.code}")
                print(f"  错误信息: {error.message}")
                error_count += 1
            except Exception as e:
                print(f"失败")
                print(f"  错误: {str(e)}")
                error_count += 1

        # 提交事务
        if success_count > 0:
            print("\n正在提交事务...")
            connection.commit()
            print("事务提交成功！")
        else:
            print("\n没有成功的语句，不提交事务")

        # 打印统计信息
        print("\n" + "="*60)
        print(f"执行完成！")
        print(f"  成功: {success_count} 条")
        print(f"  失败: {error_count} 条")
        print(f"  总计: {total_statements} 条")
        print("="*60)

    except cx_Oracle.DatabaseError as e:
        error, = e.args
        print(f"\n数据库错误:")
        print(f"  错误代码: {error.code}")
        print(f"  错误信息: {error.message}")
        if connection:
            connection.rollback()
            print("事务已回滚")
        sys.exit(1)

    except FileNotFoundError:
        print(f"\n错误: 找不到文件 {sql_file_path}")
        sys.exit(1)

    except Exception as e:
        print(f"\n发生未知错误: {str(e)}")
        if connection:
            connection.rollback()
            print("事务已回滚")
        sys.exit(1)

    finally:
        # 关闭连接
        if cursor:
            cursor.close()
        if connection:
            connection.close()
            print("\n数据库连接已关闭")

if __name__ == "__main__":
    sql_file = r"C:\path\to\your\sql_file.txt"
    execute_sql_file(sql_file)

使用步骤

1. 安装依赖

pip install cx_Oracle

2. 配置数据库连接

修改 DB_CONFIG 字典中的数据库信息：

DB_CONFIG = {
    'user': 'your_username',
    'password': 'your_password',
    'dsn': 'host:port/service_name'
}

3. 准备 SQL 文件

将需要执行的 UPDATE 语句保存到文本文件中（例如：updates.txt）

4. 执行脚本

python execute_sql_improved.py

执行示例输出

正在连接到 Oracle 数据库 192.168.117.217:1521/ORCL...
数据库连接成功！

正在解析 SQL 文件: C:\Users\gao\Desktop\test\11.txt
共找到 1 条有效的 UPDATE 语句

[1/1] (第1行) 执行中... 成功 (影响 1 行)

正在提交事务...
事务提交成功！

============================================================
执行完成！
  成功: 1 条
  失败: 0 条
  总计: 1 条
============================================================

数据库连接已关闭

方案二：使用 PL/SQL 块处理 CLOB

如果必须在 Navicat 或 SQL Developer 中执行，可以使用 PL/SQL 块：

DECLARE
    v_json CLOB;
BEGIN
    -- 初始化 CLOB
    v_json := '{"billsType":"DivsionTradeRecoBills"';
    v_json := v_json || ',"billsTypeEnumName":"BusinessDivsionTradeRecoBills"';
    v_json := v_json || ',"billsId":"DZ260112-68LA-000001"';
    -- ... 继续拼接 JSON 数据 ...
    v_json := v_json || '}';

    -- 执行 UPDATE
    UPDATE X6_SAP_GLS.X2_MSSAP_LOG t
    SET t.JSON = v_json
    WHERE t.BILLS_ID = 'DZ260112-68LA-000001';

    COMMIT;

    DBMS_OUTPUT.PUT_LINE('更新成功，影响 ' || SQL%ROWCOUNT || ' 行');
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        DBMS_OUTPUT.PUT_LINE('错误: ' || SQLERRM);
        RAISE;
END;
/

缺点：

❌ 需要手动拼接字符串，容易出错
❌ 代码冗长，可读性差
❌ 不适合批量处理

方案三：使用 SQL*Loader（批量导入）

适用于大批量数据的场景，但配置较复杂，不推荐用于少量数据更新。

核心技术原理

参数化查询的优势

# ❌ 错误方式：字符串拼接（有长度限制）
sql = f"UPDATE table SET json = '{long_json_string}'"

# ✅ 正确方式：参数化查询（无长度限制）
sql = "UPDATE table SET json = :json_data"
cursor.execute(sql, json_data=long_json_string)

工作原理：

cx_Oracle 检测到字符串超过 4000 字节
自动将字符串转换为 CLOB 类型
使用 Oracle 的绑定变量机制传递数据
避开了字符串字面量的长度限制

Oracle 字符串类型对比

类型	最大长度	适用场景
VARCHAR2	4000 字节	短文本
NVARCHAR2	2000 字符	多语言短文本
CLOB	4GB	超长文本、JSON、XML
NCLOB	4GB	多语言超长文本

常见问题 FAQ

Q1: 为什么 Navicat 不能处理？

A: Navicat 在生成 UPDATE 语句时，会将所有值直接拼接到 SQL 中作为字符串字面量，无法自动转换为 CLOB 类型。

Q2: 字段类型是 VARCHAR2(4000)，能存储 CLOB 吗？

A: 不能。如果字段类型是 VARCHAR2，需要先修改表结构：

-- 修改字段类型为 CLOB
ALTER TABLE X6_SAP_GLS.X2_MSSAP_LOG MODIFY (JSON CLOB);

Q3: cx_Oracle 需要安装 Oracle 客户端吗？

A: 需要。请先安装 Oracle Instant Client：

Windows:

# 下载 Oracle Instant Client
# https://www.oracle.com/database/technologies/instant-client/downloads.html

# 解压到目录，例如：C:\oracle\instantclient_19_8

# 添加到 PATH 环境变量
set PATH=C:\oracle\instantclient_19_8;%PATH%

Linux:

# 下载并解压
wget https://download.oracle.com/otn_software/linux/instantclient/instantclient-basic-linux.x64-19.8.0.0.0dbru.zip
unzip instantclient-basic-linux.x64-19.8.0.0.0dbru.zip
sudo mv instantclient_19_8 /opt/oracle/

# 配置环境变量
export LD_LIBRARY_PATH=/opt/oracle/instantclient_19_8:$LD_LIBRARY_PATH

Q4: 如何检查 JSON 字符串的字节数？

import json

json_string = '{"key": "value"}'
byte_size = len(json_string.encode('utf-8'))
print(f"字节数: {byte_size}")

# 如果超过 4000 字节，必须使用参数化查询
if byte_size > 4000:
    print("⚠️ 超过 4000 字节，Navicat 无法处理！")

Q5: 可以用 JDBC 或其他语言实现吗？

A: 可以！所有支持参数化查询的数据库驱动都可以：

Java (JDBC):

String sql = "UPDATE table SET json = ? WHERE id = ?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, longJsonString);  // 自动处理 CLOB
pstmt.setString(2, billsId);
pstmt.executeUpdate();

Node.js (oracledb):

const sql = "UPDATE table SET json = :json WHERE id = :id";
await connection.execute(sql, {
    json: longJsonString,  // 自动处理 CLOB
    id: billsId
});

性能优化建议

1. 批量提交

# 每 1000 条提交一次
for idx, sql in enumerate(sql_statements):
    cursor.execute(sql)
    if (idx + 1) % 1000 == 0:
        connection.commit()
        print(f"已提交 {idx + 1} 条")

connection.commit()  # 最后一次提交

2. 使用 executemany（批量执行）

sql = "UPDATE table SET json = :json WHERE id = :id"
data = [
    {'json': json1, 'id': 'ID001'},
    {'json': json2, 'id': 'ID002'},
    # ...
]
cursor.executemany(sql, data)
connection.commit()

3. 禁用日志（仅限测试环境）

-- 临时禁用表的日志记录（加快速度）
ALTER TABLE X6_SAP_GLS.X2_MSSAP_LOG NOLOGGING;

-- 执行更新...

-- 恢复日志记录
ALTER TABLE X6_SAP_GLS.X2_MSSAP_LOG LOGGING;

总结

最佳实践

✅ 优先使用参数化查询：避免字符串长度限制和 SQL 注入
✅ 字段类型使用 CLOB：存储超长文本数据
✅ 批量操作要分批提交：避免占用过多资源
✅ 添加错误处理和日志：方便排查问题

不推荐做法

❌ 在 Navicat 中手动拼接超长 SQL
❌ 将 JSON 拆分为多个字段存储
❌ 使用字符串拼接构建 SQL（SQL 注入风险）

工具对比

工具	处理超长字符串	批量执行	易用性	推荐度
Python + cx_Oracle	✅	✅	⭐⭐⭐⭐⭐	⭐⭐⭐⭐⭐
Java + JDBC	✅	✅	⭐⭐⭐⭐	⭐⭐⭐⭐
PL/SQL 块	✅	❌	⭐⭐⭐	⭐⭐⭐
Navicat 直接执行	❌	❌	⭐⭐⭐⭐⭐	⭐

参考资料

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

oracle

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

oracle

登录后参与评论

0 条评论

热度