Closed liheng314 closed 3 months ago
hi @liheng314 example code:
package main
import (
"database/sql"
"fmt"
_ "github.com/sijms/go-ora/v2"
"os"
"time"
)
func execCmd(db *sql.DB, stmts ...string) error {
for _, stmt := range stmts {
if _, err := db.Exec(stmt); err != nil {
if len(stmts) > 1 {
return fmt.Errorf("error: %v in execuation of stmt: %s", err, stmt)
} else {
return err
}
}
}
return nil
}
func createTable(db *sql.DB) error {
return execCmd(db, `CREATE TABLE TTB_519(FILE_TEXT NCLOB)`)
}
func dropTable(db *sql.DB) error {
return execCmd(db, `DROP TABLE TTB_519 PURGE`)
}
func insert(db *sql.DB) error {
t := time.Now()
_, err := db.Exec("INSERT INTO TTB_519(FILE_TEXT) VALUES(:1)", "this is a test")
if err != nil {
return err
}
fmt.Println("finish insert 1 row: ", time.Now().Sub(t))
return nil
}
func query(db *sql.DB) error {
t := time.Now()
var res string
rows, err := db.Query("SELECT FILE_TEXT FROM TTB_519")
if err != nil {
return err
}
defer func() {
err = rows.Close()
if err != nil {
fmt.Println("can't close rows: ", err)
}
}()
for rows.Next() {
err = rows.Scan(&res)
if err != nil {
return err
}
fmt.Println("result: ", res)
}
fmt.Println("finish query: ", time.Now().Sub(t))
return rows.Err()
}
func main() {
db, err := sql.Open("oracle", os.Getenv("DSN"))
if err != nil {
fmt.Println("can't connect: ", err)
return
}
defer func() {
err = db.Close()
if err != nil {
fmt.Println("can't close connection: ", err)
}
}()
err = createTable(db)
if err != nil {
fmt.Println("Can't create table: ", err)
return
}
defer func() {
err = dropTable(db)
if err != nil {
fmt.Println("Can't drop table: ", err)
}
}()
err = insert(db)
if err != nil {
fmt.Println("can't insert data: ", err)
return
}
err = query(db)
if err != nil {
fmt.Println("can't query data: ", err)
return
}
}
result:
finish insert 1 row: 127.048971ms
result: this is a test
finish query: 231.521123ms
@sijms I executed your code and still got the same error. The version is the latest v2.8.9.
when I downgrade the version to v2.8.7, the code worked well.
Oracle version is 'Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production'
Yes the issue occur with oracle 11
the issue happen because LOB is retrieved from server as LONG types in case of lob pre-fetch mode (default) oracle 11 interpret LONG type as number so I change Long to LongVarChar which work with oracle 11 and 21
I have a talbe TEST and it has a colum FILE_TEXT whose Type is CLOB. I execute code below and get an error: ORA-00932: inconsistent datatypes: expected NUMBER got CLOB using the latest v2.8.9. v2.5.3 seems to be ok.
func TestB(t *testing.T) { conn, err := sql.Open("oracle", "XXX://XXX:XXX@XXX:1521/XXX") if err != nil { panic(err) } var res string rows, err := conn.Query("SELECT t.FILE_TEXT FROM TEST.TEST t ") // error here: ORA-00932: inconsistent datatypes: expected NUMBER got CLOB for rows.Next() { err = rows.Scan(&res) } }