go-ora: Custom types returned by select queries don't satisfy the documented requirements of the Scanner interface

According to the docs of the Scanner interface in the database/sql package:

// The src value will be of one of the following types: // // int64 // float64 // bool // []byte // string // time.Time // nil - for NULL values

However, this code assigns a TimeStamp to the underlying value. Unfortunately, this seems to be incompatible with packages such as encoding/json. Here’s a self-contained example (I omitted all the error handling to keep it simple):

> docker run --rm -p 1521:1521 -e ORACLE_PASSWORD=testpass --name oracle gvenzl/oracle-xe:slim-faststart
> docker exec -it oracle bash
>> sqlplus
>> # user: system / password: testpass
>>> CREATE TABLE FOOBAR (TS TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
>>> INSERT INTO FOOBAR VALUES(DEFAULT);
>>> SELECT * FROM FOOBAR;
>>> exit

package main

import (
	"database/sql"
	"encoding/json"
	"fmt"

	_ "github.com/sijms/go-ora/v2"
)

func main() {
	db, _ := sql.Open("oracle", "oracle://system:testpass@localhost:1521/XE")

	rows, _ := db.Query("SELECT * FROM FOOBAR")

	cols, _ := rows.Columns()

	values := make([]any, len(cols))
	valuesWrapped := make([]any, 0, len(cols))
	for i := range cols {
		valuesWrapped = append(valuesWrapped, &values[i])
	}
	_ = rows.Next()
	_ = rows.Scan(valuesWrapped...)

	jObj := map[string]any{}
	for i, v := range values {
		jObj[cols[i]] = v
	}
	b, _ := json.Marshal(jObj)

	fmt.Println(string(b))
}

which outputs {"TS":{}}.

However, if I try the same code using github.com/godror/godror like so:

package main

import (
	"database/sql"
	"encoding/json"
	"fmt"

	_ "github.com/godror/godror"
)

func main() {
	db, _ := sql.Open("godror", "oracle://system:testpass@localhost:1521/XE")

	rows, _ := db.Query("SELECT * FROM FOOBAR")

	cols, _ := rows.Columns()

	values := make([]any, len(cols))
	valuesWrapped := make([]any, 0, len(cols))
	for i := range cols {
		valuesWrapped = append(valuesWrapped, &values[i])
	}
	_ = rows.Next()
	_ = rows.Scan(valuesWrapped...)

	jObj := map[string]any{}
	for i, v := range values {
		jObj[cols[i]] = v
	}
	b, _ := json.Marshal(jObj)

	fmt.Println(string(b))
}

I get {"TS":"2022-12-24T13:43:39.423782Z"} because this code uses time.Time as the underlying type.

Note: I used DYLD_LIBRARY_PATH=./instantclient_19_8 which I downloaded from here.

I’m just speculating now, since I only have superficial knowledge of how various Golang database drivers are implemented, but maybe go-ora needs to do some extra conversions in DataSet.Next(), just like godror does in rows.Next.

I guess I can work around this by adding some go-ora custom type assertions when constructing jObj, but that’s very inconvenient…

PS: Thank you for writing and maintaining this amazing package! ❤️