Insert custom date

[tredoe]

Describe the bug
Can not insert a custom date:

package main

import (
	"database/sql"
	"fmt"
	"log"

	"gopkg.in/goracle.v2"
	_ "gopkg.in/goracle.v2"
)

const (
	dbname = "demo0"
	user   = "demo"
	pass   = "***"
)

func main() {
	db, err := connectDB()
	if err != nil {
		log.Fatal("Connect Error: %s", err)
	}
	if err = db.Ping(); err != nil {
		log.Fatalf("Ping Error: %s", err)
	}
	defer db.Close()
	fmt.Println("Connected: ok")

	if _, err = db.Exec(table); err != nil {
		log.Fatalf("Create Error: %s", err)
	}
	if _, err = db.Exec(insert); err != nil {
		log.Fatalf("Insert Error: %s", err)
	}
	if _, err = db.Exec("drop table foo"); err != nil {
		log.Fatalf("Drop Error: %s", err)
	}
}

func connectDB() (*sql.DB, error) {
	//driver := "goracle"
	connString := fmt.Sprintf("oracle://%s:%s@127.0.0.1/%s", user, pass, dbname)
	//return sql.Open(driver, connString)

	conn, err := goracle.NewConnector(
		connString,
		goracle.NewSessionIniter(
			map[string]string{"NLS_DATE_FORMAT": "YYYY-MM-DD Hh24:MI:SS"},
		),
	)

	return sql.OpenDB(conn), err
}

const (
	table = `
CREATE TABLE foo (
    id smallint  PRIMARY KEY NOT NULL,
    tm timestamp NOT NULL
)
`
	insert = `
INSERT INTO foo
    (id, tm)
  VALUES
    (0, '2019-11-22 11:15:53')
`
)

To Reproduce
Steps to reproduce the behavior:

1. go run test_oracle.go
2. See error:

Connected: ok
2019/11/22 12:03:52 Insert Error: dpiStmt_execute(mode=32 arrLen=-1): ORA-01843: not a valid month

Your oracle client version
Oracle Database 18c Express Edition Release 18.0.0.0.0

Your goracle version
v2.22.4

Your go version
go version go1.13.4 windows/amd64

Your gcc version
gcc --version
gcc (GCC) 4.8.3

Machine:

• OS: Windows
• Architecture amd64
• Version: 10

[tgulacsi]

If the DB's NLS_DATE_FORMAT would be 'YYYY-MM-DD HH24:MI:SS', then the SessionIniter wouldn't be needed - so I assume that tha DB's NLS_DATE_FORMAT is different. What is it?

I'd check whether the SessionIniter works by querying v$nls_parameters and printing it.

Anyway, maybe these aren't relevant at all - TIMSTAMP is not DATE, and though Oracle implicitly converts everything (sadly), and it can convert DATE to TIMESTAMP, TIMESTAMP's format IS NOT NLS_DATE_FORMAT!

[tredoe]

I added the next code to show the value v$nls_parameters

const selectDateFormat = `SELECT value FROM v$nls_parameters WHERE parameter ='NLS_DATE_FORMAT'`

var value string
if err = db.QueryRow(selectDateFormat).Scan(&value); err != nil {
	log.Fatalf("Error: %s", err)
}
fmt.Printf("Value: %s\n", value)

And when I use:

goracle.NewSessionIniter(
	map[string]string{"NLS_DATE_FORMAT": "YYYY-MM-DD Hh24:MI:SS"},
),`

it shows: Value: YYYY-MM-DD Hh24:MI:SS else,
it shows: Value: DD/MM/RR

[tredoe]

Solved!

There is to use NLS_TIMESTAMP_FORMAT for TIMESTAMP.

[tgulacsi]

So, does it change the format as wanted? (BTW HH24, NOT Hh24)

[tredoe]

@tgulacsi Yes, it's working very well. Thanks!