使用database/sql包向Oracle表批量插入数据失败问题排查

问题场景 #

使用Go的database/sql包向Oracle表批量插入多行数据，已确认数据库连接正常、生成的SQL语句格式正确，但执行时返回错误：Bulk insert failed: EOF。

原代码片段：

db, err := sql.Open("oracle", connectionUrl)
if err != nil {
    e.logger.Error("Can't create connection: " + err.Error())
    return err
}

valueStrings := make([]string, 0, len(metrics))
valueArgs := make([]interface{}, 0, len(metrics)*9)
for _, post := range metrics {
    valueStrings = append(valueStrings, "(?, ?, ?, ?, ?, ?, ?, ?, ?)")
    valueArgs = append(valueArgs, post.Timestamp)
    valueArgs = append(valueArgs, post.MetricName)
    valueArgs = append(valueArgs, post.MetricType)
    valueArgs = append(valueArgs, post.MetricUnit)
    valueArgs = append(valueArgs, post.MetricDescription)
    valueArgs = append(valueArgs, post.MetricValue)
    valueArgs = append(valueArgs, post.Host)
    valueArgs = append(valueArgs, post.ResourceAttributes)
    valueArgs = append(valueArgs, post.MetricAttributes)
}

sql := fmt.Sprintf("INSERT INTO my_table(TS, NAME, TYPE, UNIT, DESCRIPTION, VALUE, HOST, RESOURCE_ATTRIBUTES, METRIC_ATTRIBUTES) VALUES %s",
    strings.Join(valueStrings, ","))
logger.Info("#### Insert sql : " + sql)
logger.Info("### ValueArgs :")
fmt.Println(valueArgs)

result, err := db.Exec(sql, valueArgs...)
if err != nil {
    e.logger.Error("Bulk insert failed: " + err.Error())
    return err
}

核心问题与修改建议 #

1. 修正占位符格式（关键） #

Oracle官方驱动（如godror）不支持MySQL风格的?占位符，必须使用:n格式的序号占位符。原代码中的?会导致驱动解析SQL失败，触发EOF错误。

修改后的代码（适配Oracle占位符）：

db, err := sql.Open("godror", connectionUrl) // 确保使用正确的Oracle驱动，如godror
if err != nil {
    e.logger.Error("Can't create connection: " + err.Error())
    return err
}
defer db.Close() // 新增：确保连接最终关闭

valueStrings := make([]string, 0, len(metrics))
valueArgs := make([]interface{}, 0, len(metrics)*9)
paramIdx := 1

for _, post := range metrics {
    // 为每行生成连续序号的占位符
    rowPlaceholders := fmt.Sprintf("(:%d, :%d, :%d, :%d, :%d, :%d, :%d, :%d, :%d)",
        paramIdx, paramIdx+1, paramIdx+2, paramIdx+3, paramIdx+4,
        paramIdx+5, paramIdx+6, paramIdx+7, paramIdx+8)
    valueStrings = append(valueStrings, rowPlaceholders)
    
    valueArgs = append(valueArgs, post.Timestamp)
    valueArgs = append(valueArgs, post.MetricName)
    valueArgs = append(valueArgs, post.MetricType)
    valueArgs = append(valueArgs, post.MetricUnit)
    valueArgs = append(valueArgs, post.MetricDescription)
    valueArgs = append(valueArgs, post.MetricValue)
    valueArgs = append(valueArgs, post.Host)
    valueArgs = append(valueArgs, post.ResourceAttributes)
    valueArgs = append(valueArgs, post.MetricAttributes)
    
    paramIdx += 9 // 更新参数序号，确保下一行占位符连续
}

sql := fmt.Sprintf("INSERT INTO my_table(TS, NAME, TYPE, UNIT, DESCRIPTION, VALUE, HOST, RESOURCE_ATTRIBUTES, METRIC_ATTRIBUTES) VALUES %s",
    strings.Join(valueStrings, ","))
logger.Info("#### Insert sql : " + sql)

result, err := db.Exec(sql, valueArgs...)
if err != nil {
    e.logger.Error("Bulk insert failed: " + err.Error())
    return err
}

2. 改用Oracle原生批量插入语法（可选优化） #

使用Oracle的INSERT ALL语法，更符合Oracle的批量插入规范：

// 构建INSERT ALL语句
var sqlBuilder strings.Builder
sqlBuilder.WriteString("INSERT ALL ")

paramIdx := 1
valueArgs := make([]interface{}, 0, len(metrics)*9)
for _, post := range metrics {
    sqlBuilder.WriteString(fmt.Sprintf(
        "INTO my_table(TS, NAME, TYPE, UNIT, DESCRIPTION, VALUE, HOST, RESOURCE_ATTRIBUTES, METRIC_ATTRIBUTES) VALUES (:%d, :%d, :%d, :%d, :%d, :%d, :%d, :%d, :%d) ",
        paramIdx, paramIdx+1, paramIdx+2, paramIdx+3, paramIdx+4,
        paramIdx+5, paramIdx+6, paramIdx+7, paramIdx+8))
    // 追加参数
    valueArgs = append(valueArgs, post.Timestamp, post.MetricName, post.MetricType, post.MetricUnit, post.MetricDescription, post.MetricValue, post.Host, post.ResourceAttributes, post.MetricAttributes)
    paramIdx +=9
}
sqlBuilder.WriteString("SELECT * FROM DUAL")

sql := sqlBuilder.String()
result, err := db.Exec(sql, valueArgs...)
// 后续错误处理

3. 额外排查点 #

• 确保使用的Oracle驱动为最新版本（推荐github.com/godror/godror），旧版本可能存在批量插入的兼容性问题。
• 验证参数类型与表字段完全匹配：比如Timestamp需为time.Time类型，JSON类字段需转为字符串或Oracle的JSON类型。
• 检查数据库连接超时设置，避免执行过程中连接被主动断开。

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