Chinese character reading error, ORA-01406 error occurred

[linlexing]

Since b7ff33f1bacfdc8bf9b1ff0cb42ab06f8f5b0386 chinese character reading error, ORA-01406 error occurred.

Example below:

package main

import (
    "database/sql"
    "log"

    _ "github.com/mattn/go-oci8"
)

func main() {
    // os.Setenv("NLS_LANG", "AMERICAN_AMERICA.AL32UTF8")
    // os.Setenv("NLS_LANG", "SIMPLIFIED CHINESE_CHINA.ZHS16GBK")
    db, err := sql.Open("oci8", "xxx/xxx@localhost:1521/orcl")
    log.SetFlags(log.Llongfile)
    if err != nil {
        log.Panic(err)
    }
    var str []byte
    db.Exec("drop table tmp_char")
    if _, err := db.Exec("create table tmp_char(a varchar2(3 char))"); err != nil {
        log.Panic(err)
    }
    if _, err := db.Exec("insert into tmp_char(a)values('一二三')"); err != nil {
        log.Panic(err)
    }
    defer func() {
        if _, err := db.Exec("drop table tmp_char"); err != nil {
            log.Panic(err)
        }

    }()
    if err = db.QueryRow("select a from tmp_char").Scan(&str); err != nil {
        log.Panic(err)
    }
    println("size", len(str))

}

This prints as:

main.go:34: ORA-01406: 提取的列值被截断

[MichaelS11]

Have not been able to get it to fail so far with the tests I have added:

https://github.com/MichaelS11/go-oci8/tree/temp

Does it still fail if you changed var str []byte to var str string?

[linlexing]

Does it still fail if you changed var str []byte to var str string?

Yes

[MichaelS11]

Can you print os.Getenv("NLS_LANG") and os.Getenv("NLS_NCHAR") and post the output?

Will look more into reproducing the issue tomorrow.

[linlexing]

I added the statement：

println("NLS_LANG:", os.Getenv("NLS_LANG"), "NLS_NCHAR:", os.Getenv("NLS_NCHAR"))

This output is:

NLS_LANG:  NLS_NCHAR:

I uncommented any of the following lines and printed it correctly, but the execution still went wrong：

// os.Setenv("NLS_LANG", "AMERICAN_AMERICA.AL32UTF8")
// os.Setenv("NLS_LANG", "SIMPLIFIED CHINESE_CHINA.ZHS16GBK")

I also tried running the test at the github.com/MichaelS11/go-oci8/tree/temp:

go test -v github.com/Michaels11/go-oci8 -args -disableDatabase=false -hostValid localhost/orcl -username xxx -password "xxx"

Output is:

...
=== RUN   TestTemp
--- FAIL: TestTemp (0.03s)
    oci8Sql_test.go:276: get rows error: rows error: ORA-01406: 提取的列值被截断
         - query: select A, B, C from VARCHAR2_CHAR2_20181204070229 order by A
...

My operating system and database character set is:

Windows10 64bit
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
SQL> select userenv('language') from dual;

USERENV('LANGUAGE')
----------------------------------------------------
SIMPLIFIED CHINESE_CHINA.ZHS16GBK

[MichaelS11]

After doing set NLS_LANG=SIMPLIFIED CHINESE_CHINA.ZHS16GBK can get the tests to fail. Example:

--- FAIL: TestTemp (0.04s)
        oci8SqlString_test.go:3100: insert error: exec - row 0 - error: ORA-01461: ���ܰ���Ҫ���� LONG �е� LONG ֵ

The error is different though.

What happens if you set NLS_LANG environment variable to AMERICAN_AMERICA.AL32UTF8 before running the Go test? Still get ORA-01406?

[linlexing]

Execute the script in the cmd window:

set NLS_LANG=AMERICAN_AMERICA.AL32UTF8
go test -v github.com/Michaels11/go-oci8 -args -disableDatabase=false -hostValid localhost/orcl -username xxx -password "xxx"

Result is:

--- FAIL: TestTemp (0.04s)
    oci8Sql_test.go:276: get rows error: rows error: ORA-01406: fetched column value was truncated
         - query: select A, B, C from VARCHAR2_CHAR2_20181205011405 order by A

The same error occurred, just turned into an English error message

[MichaelS11]

Not able to reproduce the issue on my computer so having a hard time finding where the issue is located. Would think that setting NLS_LANG=AMERICAN_AMERICA.AL32UTF8 should fix the issue but looks like it did not. Not sure what to try next at this moment.

Anyone else have any thoughts?

[linlexing]

@MichaelS11 I created a new database with the character set SIMPLIFIED CHINESE_CHINA.AL32UTF8 and tested it with no errors. So, can you create a database with the character set: ZHS16GBK for testing, thank you.

[MichaelS11]

@linlexing My apologies for the long delay, finally got a database created with the ZHS16GBK character set.

I feel like the root of the problem is with the C OCI driver but really not sure. But whatever the root cause is, going to see if doubling the buffer size will fix the issue. Could you please test with the below PR?

https://github.com/mattn/go-oci8/pull/317

[MichaelS11]

@thigaz Would you like to test https://github.com/mattn/go-oci8/pull/317 as well?

[linlexing]

Yes，the #317 looks like there is no error.

[MichaelS11]

@linlexing Thank you. Please close this.

[MichaelS11]

@mattn Please close this.