Use bulkinsert for clob type data, if the length is greater than 32767, an error will be reported: ORA-03146: invalid buffer length for TTC field

[sijms]

sorry for late code:

package main

import (
    "database/sql"
    "fmt"
    _ "github.com/sijms/go-ora/v2"
    "os"
    "time"
)

func createTable(conn *sql.DB) error {
    t := time.Now()
    sqlText := `CREATE TABLE TTB_415(
    ID  number(10)  NOT NULL,
    DATA  CLOB,
    PRIMARY KEY(ID)
    )`
    _, err := conn.Exec(sqlText)
    if err != nil {
        return err
    }
    fmt.Println("Finish create table TTB_415 :", time.Now().Sub(t))
    return nil
}

func dropTable(conn *sql.DB) error {
    t := time.Now()
    _, err := conn.Exec("drop table TTB_415 purge")
    if err != nil {
        return err
    }
    fmt.Println("Finish drop table: ", time.Now().Sub(t))
    return nil
}

func Insert(conn *sql.DB) error {
    t := time.Now()
    sqlText := `INSERT INTO TTB_415 (ID, DATA) VALUES(:ID, :DATA)`
    // create structure
    type data struct {
        Id   int    `db:"ID"`
        Data string `db:"DATA,clob"`
    }
    tempData, err := os.ReadFile("clob.json")
    if err != nil {
        return err
    }
    fileData := string(tempData) + string(tempData) + string(tempData) + string(tempData) + string(tempData) +
        string(tempData) + string(tempData) + string(tempData)
    fmt.Println("data size: ", len(fileData))
    count := 10
    datas := make([]data, count)
    for x := 0; x < count; x++ {
        datas[x].Id = x + 1
        datas[x].Data = string(fileData)
    }
    _, err = conn.Exec(sqlText, datas)
    if err != nil {
        return err
    }
    fmt.Println("Finish insert data: ", time.Now().Sub(t))
    return nil
}
func main() {
    conn, err := sql.Open("oracle", os.Getenv("DSN"))
    if err != nil {
        fmt.Println("can't connect: ", err)
        return
    }
    defer func() {
        err = conn.Close()
        if err != nil {
            fmt.Println("can't close connection: ", err)
        }
    }()

    err = createTable(conn)
    if err != nil {
        fmt.Println("Can't create table: ", err)
        return
    }
    defer func() {
        err = dropTable(conn)
        if err != nil {
            fmt.Println("Can't drop table: ", err)
        }
    }()
    err = Insert(conn)
    if err != nil {
        fmt.Println("Can't insert: ", err)
    }
}

result:

Finish create table GOORA_TEMP_VISIT : 220.821374ms
data size:  74664
Finish insert data:  2.471298145s
Finish drop table:  865.647335ms

Process finished with the exit code 0

the clob.json for data

as you see code is pass without error inserting 10 rows with data size 74 kb also note: 1- if you use large data you should use Clob and Blob data types 2- In the above example I use structure pars with tag and pass data as array