CLOB values truncated

[linlexing]

Since b7ff33f1bacfdc8bf9b1ff0cb42ab06f8f5b0386 CLOBs are no longer working properly.

Example below:

package main

import (
    "database/sql"
    "log"
    "os"

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

func main() {
    db, err := sql.Open("oci8", "xxx/xxx@localhost:1521/orcl")
    if err != nil {
        log.Panic(err)
    }
    var str []byte
    if err = db.QueryRow("select concat(to_clob(rpad('*', 3000, '*')),to_clob(rpad('*', 3000, '*'))) from dual").Scan(&str); err != nil {
        log.Panic(err)
    }
    if len(str) != 6000 {
        println("error size", len(str))
    }
}

This prints as:

error size 4000

[MichaelS11]

So far have only found this to be an issue with dual. I tried switching from OCILobRead to OCILobRead2 and it had the same issue. From what I can tell, lob from dual maxes out at 4000 characters for OCI. Does anyone know if that is true or not?

If one can select lob from dual with more than 4000 characters, not sure where the issue is located. Select from table with more than 4000 characters seems to work fine, which makes me think the Go code in general is fine, just something in particular with dual.

Temp testing/changed code is located here: https://github.com/MichaelS11/go-oci8/tree/temp

[cjbj]

From what I can tell, lob from dual maxes out at 4000 characters for OCI. Does anyone know if that is true or not?

Not true. For example in PHP OCI8:

$s = oci_parse($c, "select concat(to_clob(rpad('*', 3000, '*')),to_clob(rpad('*', 3000, '*'))) as X from dual");
$r = oci_execute($s);
$row = oci_fetch_array($s, OCI_ASSOC+OCI_RETURN_NULLS);
var_dump(strlen($row['X']->load()));

gives:

int(6000)

[linlexing]

Select from table with more than 4000 characters seems to work fine, which makes me think the Go code in general is fine, just something in particular with dual.

It doesn't seem right.Other example:

package main

import (
    "database/sql"
    "log"

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

func main() {
    // os.Setenv("NLS_LANG", "AMERICAN_AMERICA.AL32UTF8")
    db, err := sql.Open("oci8", "tjbm/llx123@localhost:1521/orcl")
    log.SetFlags(log.Llongfile)
    if err != nil {
        log.Panic(err)
    }
    var str []byte
    db.Exec("drop table tmp_clob")
    if _, err := db.Exec("create table tmp_clob(a clob)"); err != nil {
        log.Panic(err)
    }
    if _, err := db.Exec("insert into tmp_clob(a)values(concat(to_clob(rpad('*', 3000, '*')),to_clob(rpad('*', 3000, '*'))))"); err != nil {
        log.Panic(err)
    }
    defer func() {
        if _, err := db.Exec("drop table tmp_clob"); err != nil {
            log.Panic(err)
        }

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

}

This prints as:

size 4000

[MichaelS11]

Part of my issue was I forgot that 4000 is the max number of characters for to_clob(rpad('a', 4000, 'a')) and that concat is required to get a clob with more than 4000 characters. Thank you for your help.

I think switching to OCILobRead2 fixed it. https://github.com/mattn/go-oci8/pull/305

[MichaelS11]

@linlexing Please test and then close if fixed.

[linlexing]

Yes.This bug has been fixed, thank you very much.

[MichaelS11]

Welcome, thank you! :)