Readme Output Parameters Example Does Not Work

[njdullea]

I am testing the readme output parameters example.

Below is code from a functioning test, some details are removed or abstracted. The local oracledb used has a table TestTable which has two columns: NAME and ID, and there is one record with a random name and ID of 1.

func TestOracleSelectInto(t *testing.T) {
    // setup url
    db, _ := sql.Open("oracle", url)
    defer db.Close()

    var name sql.NullString

    // ORA-01006: bind variable does not exist
    _, err := db.Exec(`SELECT NAME INTO :pr1 FROM TestTable WHERE ID=1`, go_ora.Out{Dest: &name, Size: 100})
    if err != nil {
        fmt.Println("Err happened: ", err)
        return
    }

    fmt.Println("Name: ", name)
}

When executing the statement, it returns ORA-01006: bind variable does not exist, but based on the example I would expect it to get the name from the record. I'm unsure if I am missing Oracle specific setup because I am new to Oracle and it is not in the example, or if there is an issue with the example. Thank you for any help you can provide.

Versions:

• go_ora: github.com/sijms/go-ora/v2 v2.7.23
• Oracle: 19c enterprise edition

[sijms]

output pars

package main

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

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,
    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) 
VALUES(:1, :2, :3, :4)`)
    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())
        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 INTO :1, :2, :3, :4 FROM GOORA_TEMP_VISIT WHERE VISIT_ID = 1;
END;`
    var (
        id   int64
        name sql.NullString
        val  float64
        date sql.NullTime
    )
    //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})
    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))

    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
    }

}

output

Finish create table GOORA_TEMP_VISIT : 209.686137ms
100 rows inserted:  11.687775275s
ID:  1
Name:  {abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ true}
Val:  1.1

[sijms]

I think you forget to use BEGIN ... END; inside sql

[njdullea]

Thank you for your response. I did forget BEGIN ... END; (the output parameters example also doesn't have it which confused me) and there were a couple other complications from switching between testing in code vs datagrip. The code you posted helped me resolve that, and I appreciate your help a lot!