如何解决Oracle Express 21c插入MultiPolygons数据时的ORA-00939错误？

• 持有包含大量顶点的MultiPolygon类型GeoJSON数据
• 使用Python脚本向Oracle空间表插入数据时，触发ORA-00939: 函数参数过多错误
• 已尝试预编译语句绑定参数，问题未解决

原代码片段 #

id = regs[each["properties"]["REGION"].upper()]["id"]
prov_id = regs[each["properties"]["REGION"].upper()]["PROV_ID"]
p_code = regs[each["properties"]["REGION"].upper()]["P_CODE"]
r_code = regs[each["properties"]["REGION"].upper()]["R_CODE"]
region = each["properties"]["REGION"].upper()

script = """
    INSERT INTO regions(id, id_province, p_code, r_code, nom, geom) 
    VALUES(""" + id + """, """ + prov_id + """,'""" + p_code + """','""" + r_code + """','""" + region + """', SDO_GEOMETRY(
              2007,
              8307,
              NULL,
              SDO_ELEM_INFO_ARRAY(""" + sd_elem_info[2] + """),
              SDO_ORDINATE_ARRAY(""" + str_coordinates + """
        ))
    """
cursor.execute(script)

错误信息 #

Traceback (most recent call last):
File "d:\Stage\mdg_shp_trusted\region.py", line 111, in cursor.execute(script)
File "C:\Python312\Lib\site-packages\oracledb\cursor.py", line 710, in execute impl.execute(self)
File "src\oracledb\impl/thin/cursor.pyx", line 196, in oracledb.thin_impl.ThinCursorImpl.execute
File "src\oracledb\impl/thin/protocol.pyx", line 440, in oracledb.thin_impl.Protocol._process_single_message
File "src\oracledb\impl/thin/protocol.pyx", line 441, in oracledb.thin_impl.Protocol._process_single_message
File "src\oracledb\impl/thin/protocol.pyx", line 433, in oracledb.thin_impl.Protocol._process_message
File "src\oracledb\impl/thin/messages.pyx", line 74, in oracledb.thin_impl.Message._check_and_raise_exception
oracledb.exceptions.DatabaseError: ORA-00939: 函数参数过多

1. 直接使用SDO_UTIL.FROM_GEOJSON导入 #

Oracle Spatial提供SDO_UTIL.FROM_GEOJSON函数，可直接将GeoJSON字符串转换为SDO_GEOMETRY对象，完全避开手动拆分坐标数组的参数限制问题，是最简洁的解决方案。

示例代码：

import oracledb
import json

# 遍历GeoJSON Feature
for each in geojson_features:
    region_name = each["properties"]["REGION"].upper()
    # 获取属性数据
    id = regs[region_name]["id"]
    prov_id = regs[region_name]["PROV_ID"]
    p_code = regs[region_name]["P_CODE"]
    r_code = regs[region_name]["R_CODE"]
    
    # 提取当前Feature的Geometry部分并转为JSON字符串
    geom_json_str = json.dumps(each["geometry"])
    
    # 预编译插入语句，绑定所有参数
    insert_sql = """
        INSERT INTO regions(id, id_province, p_code, r_code, nom, geom)
        VALUES (:id, :prov_id, :p_code, :r_code, :region, SDO_UTIL.FROM_GEOJSON(:geom_json))
    """
    
    cursor.execute(insert_sql, {
        "id": id,
        "prov_id": prov_id,
        "p_code": p_code,
        "r_code": r_code,
        "region": region_name,
        "geom_json": geom_json_str
    })

# 提交事务
connection.commit()

2. 拆分坐标数组（手动构造SDO_GEOMETRY时使用） #

若因特殊原因无法使用FROM_GEOJSON，需注意Oracle的SDO_ORDINATE_ARRAY单函数调用参数上限为1000个。可将坐标数组拆分为多个分段，通过MULTISET UNION ALL合并后传入：

示例思路：

# 假设str_coordinates是逗号分隔的坐标字符串，转为浮点列表
coords = list(map(float, str_coordinates.split(",")))
# 按每998个坐标拆分（保证每个分段都是完整的点对）
coord_chunks = [coords[i:i+998] for i in range(0, len(coords), 998)]
# 构造多个SDO_ORDINATE_ARRAY片段
ordinate_fragments = ", ".join([f"SDO_ORDINATE_ARRAY({','.join(map(str, chunk))})" for chunk in coord_chunks])

# 构造插入语句，用MULTISET UNION ALL合并坐标数组
insert_sql = f"""
    INSERT INTO regions(id, id_province, p_code, r_code, nom, geom)
    VALUES (:id, :prov_id, :p_code, :r_code, :region, SDO_GEOMETRY(
        2007,
        8307,
        NULL,
        SDO_ELEM_INFO_ARRAY(:sd_elem_info),
        MULTISET UNION ALL ({ordinate_fragments})
    ))
"""

# 执行语句（需确保SDO_ELEM_INFO与拆分后的坐标匹配）
cursor.execute(insert_sql, {
    "id": id,
    "prov_id": prov_id,
    "p_code": p_code,
    "r_code": r_code,
    "region": region_name,
    "sd_elem_info": sd_elem_info[2]
})

3. 批量插入优化（大数据量场景） #

若数据量极大，可使用oracledb的executemany方法批量提交，或结合Oracle SQL*Loader工具导入，提升插入效率同时避免单条语句参数过载。

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