Error when using `sql.NullIntX` types for output parameters.

When using an output parameter of type sql.NullInt64 there is an error: ORA-06502: PL/SQL: numeric or value error. If you replace the type with int64 it will start to work again. It looks like when the driver is decoding the parameter value in GetClr() it is missing data because the chunk size is 1 when it should be 2.

The error happens for any int sizes: int16 -> nullInt16, int32 -> nullInt32 etc...

Here is testing code where it does not work. In queryOutputPars replace someNumber sql.NullInt64 with someNumber int64 and it will start working again.

func createTable(conn *sql.DB) error {
    t := time.Now()
    sqlText := `CREATE TABLE GOORA_TEMP_VISIT(
    VISIT_ID    number(10)  NOT NULL,
    NAME        VARCHAR(200),
    VAL         number(10,2),
    VISIT_DATE  date,
    SOME_NUMBER number(10),
    PRIMARY KEY(VISIT_ID)
    )`
    _, err := conn.Exec(sqlText)
    if err != nil {
        return err
    }
    fmt.Println("Finish create table GOORA_TEMP_VISIT :", time.Now().Sub(t))
    return nil
}

func insertData(conn *sql.DB) error {
    t := time.Now()
    index := 1
    stmt, err := conn.Prepare(`INSERT INTO GOORA_TEMP_VISIT(VISIT_ID, NAME, VAL, VISIT_DATE, SOME_NUMBER) 
VALUES(:1, :2, :3, :4, :5)`)
    if err != nil {
        return err
    }
    defer func() {
        _ = stmt.Close()
    }()
    nameText := "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ"
    val := 1.1
    for index = 1; index <= 100; index++ {
        _, err = stmt.Exec(index, nameText, val, time.Now(), 6)
        if err != nil {
            return err
        }
        val += 1.1
    }
    fmt.Println("100 rows inserted: ", time.Now().Sub(t))
    return nil
}

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

func queryOutputPars(conn *sql.DB) error {
    t := time.Now()
    sqlText := `BEGIN
SELECT VISIT_ID, NAME, VAL, VISIT_DATE, SOME_NUMBER INTO :1, :2, :3, :4, :5 FROM GOORA_TEMP_VISIT WHERE VISIT_ID = 1;
END;`
    var (
        id   int64
        name sql.NullString
        val  float64
        date sql.NullTime
        // This works
        // someNumber int64
        // This does not work
        someNumber sql.NullInt64
    )
    //name = strings.Repeat(" ", 600)
    _, err := conn.Exec(sqlText, sql.Out{Dest: &id}, go_ora.Out{Dest: &name, Size: 200},
        sql.Out{Dest: &val}, sql.Out{Dest: &date}, go_ora.Out{Dest: &someNumber})
    if err != nil {
        return err
    }
    fmt.Println("ID: ", id)
    fmt.Println("Name: ", name)
    fmt.Println("Val: ", val)
    fmt.Println("Date: ", date)
    fmt.Println("Finish query output pars: ", time.Now().Sub(t))
    fmt.Println("Some number: ", someNumber)

    return nil
}

func main() {
        server = os.Getenv("DSN")
    connStr := os.ExpandEnv(server)
    if connStr == "" {
        fmt.Println("Missing server environmental variable")
        os.Exit(1)
    }
    fmt.Println("Connection string: ", connStr)
    conn, err := sql.Open("oracle", server)
    if err != nil {
        fmt.Println("Can't open connection", err)
        return
    }

    defer func() {
        err = conn.Close()
        if err != nil {
            fmt.Println("Can't close connection", err)
        }
    }()

    err = conn.Ping()
    if err != nil {
        fmt.Println("Can't ping connection", err)
        return
    }

    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 = insertData(conn)
    if err != nil {
        fmt.Println("Can't insert data", err)
        return
    }
    err = queryOutputPars(conn)
    if err != nil {
        fmt.Println("Can't get output parameters", err)
        return
    }

}

sijms / go-ora

Error when using `sql.NullIntX` types for output parameters. #487