go-ora: Error when reading many records having large field

I have found this problem using a copy of production database.

When querying a very simple table using a select * from MY_TABLE , i have a random problem that could be either:

• premature stop reading, with last read record corrupted
• hang
• error saying the paquet is not data

The problem isn’t related to the number of records. I’m able to query several million of records. It’s more related to the use of NVARCHAR2 with a large enough data.

For debugging purpose, I made a self contained sample to illustrate the problem. This program create a table similar to production:

Name      Null?    Type            
--------- -------- --------------- 
RECNUMBER NOT NULL NUMBER          
LABEL     NOT NULL NVARCHAR2(20)   
TEXT               NVARCHAR2(2000) 

Then it fills the with records having TEXT field as bigger as possible. My database is using AL32UTF8 char set. I think this is what we call UNICODE 32 bits, where each chars is encoded using 32 bits. So 2000 chars makes 4000 bytes.

Like the production application, it generates a long string to be stored into the table, using several records, and uses the maximum of the NVARCHAR2(2000) room.

package main

import (
	"database/sql/driver"
	"fmt"
	"io"
	"os"
	"strconv"
	"strings"

	go_ora "github.com/sijms/go-ora"
)

func exitOnError(err error) {
	if err != nil {
		fmt.Println(err)
		os.Exit(1)
	}
}

const (
	RecordSize = 2000
	TotalSize  = 140000
)

func main() {
	server := os.Getenv("DB_DEV1") + "?TRACE FILE=Trace.log"

	db, err := go_ora.NewConnection(server)
	err = db.Open()
	exitOnError(err)
	defer db.Close()

	err = CreateTable(db, RecordSize)
	exitOnError(err)

	rec := GenerateData(TotalSize, RecordSize)
	err = CreateRecords(db, rec)
	exitOnError(err)

	read, err := GetRecords(db)
	exitOnError(err)

	fmt.Println("Want ", len(rec), "records")
	fmt.Println("Got  ", len(read), "records")

	i := 0
	for i < len(rec) && i < len(read) {
		if rec[i] != read[i] {
			fmt.Println("Records", i, "are different")
			fmt.Println("Want\n", rec[i])
			fmt.Println("Got\n", read[i])
		}
		i++
	}
}

func CreateTable(DB *go_ora.Connection, recordSize int) error {

	stmt := go_ora.NewStmt("drop table TEST_TABLE", DB)
	_, err := stmt.Exec(nil)
	defer stmt.Close()

	stmt = go_ora.NewStmt(`CREATE TABLE TEST_TABLE 
	(
	  RECNUMBER NUMBER NOT NULL 
	, LABEL NVARCHAR2(20) NOT NULL 
	, TEXT NVARCHAR2(`+strconv.Itoa(recordSize)+`) 
	)`, DB)
	_, err = stmt.Exec(nil)

	return err
}

func CreateRecords(DB *go_ora.Connection, r []string) error {

	for i, s := range r {

		stmt := go_ora.NewStmt("insert into TEST_TABLE(RECNUMBER,LABEL,TEXT) values (:1,:2,:3)", DB)
		recLabel := fmt.Sprintf("Rec %d", i)

		stmt.AddParam("1", i, 0, go_ora.Input /* or go_ora.Output*/)
		stmt.AddParam("2", recLabel, len(recLabel), go_ora.Input /* or go_ora.Output*/)
		stmt.AddParam("3", s, len(s), go_ora.Input /* or go_ora.Output*/)
		_, err := stmt.Exec(nil)
		if err != nil {
			return err
		}
		stmt.Close()
	}
	fmt.Println("Record ", len(r), "inserted")
	return nil
}

func GetRecords(DB *go_ora.Connection) ([]string, error) {
	ret := []string{}
	stmt := go_ora.NewStmt("select RECNUMBER,LABEL,TEXT from TEST_TABLE order by RECNUMBER", DB)

	rows, err := stmt.Query(nil)
	if err != nil {
		return nil, err
	}

	values := []driver.Value{0, 0, 0}
	for {
		err = rows.Next(values)
		if err != nil {
			break
		}
		ret = append(ret, values[2].(string))
	}

	if err != io.EOF {
		return nil, err
	}
	return ret, nil
}

func GenerateData(length int, sliceOf int) []string {
	s := strings.Builder{}
	i := 0

	for s.Len() < length {
		s.WriteString(fmt.Sprintf("%010d,", i))
		i++
	}

	buf := []byte(s.String())
	ret := []string{}
	for len(buf) > 0 {
		l := len(buf)
		if l > sliceOf {
			l = sliceOf
		}
		ret = append(ret, string(buf[:l]))
		buf = buf[l:]
	}

	return ret
}

The call to GenerateData(140000, 2000) creates a dataset of 140 000 chars, sliced into 2000 chars records.

For those values, the programs fails most of the time, never at the same record.

Record  71 inserted
Want  71 records
Got   65 records
Records 64 are different
Want
 011636,0000011637,0000011638,0000011639,0000011640,0000011641,0000011642,0000011643,0000011644,0000011645,0000011646,0000011647,0000011648,0000011649,0000011650,0000011651,0000011652,0000011653,0000011654,0000011655,0000011656,0000011657,0000011658,0000011659,0000011660,0000011661,0000011662,0000011663,0000011664,0000011665,0000011666,0000011667,0000011668,0000011669,0000011670,0000011671,0000011672,0000011673,0000011674,0000011675,0000011676,0000011677,0000011678,0000011679,0000011680,0000011681,0000011682,0000011683,0000011684,0000011685,0000011686,0000011687,0000011688,0000011689,0000011690,0000011691,0000011692,0000011693,0000011694,0000011695,0000011696,0000011697,0000011698,0000011699,0000011700,0000011701,0000011702,0000011703,0000011704,0000011705,0000011706,0000011707,0000011708,0000011709,0000011710,0000011711,0000011712,0000011713,0000011714,0000011715,0000011716,0000011717,0000011718,0000011719,0000011720,0000011721,0000011722,0000011723,0000011724,0000011725,0000011726,0000011727,0000011728,0000011729,0000011730,0000011731,0000011732,0000011733,0000011734,0000011735,0000011736,0000011737,0000011738,0000011739,0000011740,0000011741,0000011742,0000011743,0000011744,0000011745,0000011746,0000011747,0000011748,0000011749,0000011750,0000011751,0000011752,0000011753,0000011754,0000011755,0000011756,0000011757,0000011758,0000011759,0000011760,0000011761,0000011762,0000011763,0000011764,0000011765,0000011766,0000011767,0000011768,0000011769,0000011770,0000011771,0000011772,0000011773,0000011774,0000011775,0000011776,0000011777,0000011778,0000011779,0000011780,0000011781,0000011782,0000011783,0000011784,0000011785,0000011786,0000011787,0000011788,0000011789,0000011790,0000011791,0000011792,0000011793,0000011794,0000011795,0000011796,0000011797,0000011798,0000011799,0000011800,0000011801,0000011802,0000011803,0000011804,0000011805,0000011806,0000011807,0000011808,0000011809,0000011810,0000011811,0000011812,0000011813,0000011814,0000011815,0000011816,0000011817,00
Got
 011636,0000011637,0000011638,0000011639,0000011640,0000011641,0000011642,0000011643,0000011644,0000011645,0000011646,0000011647,0000011648,0000011649,0000011650,0000011651,0000011652,0000011653,0000011654,0000011655,0000011656,0000011657,000001165  㠀Ⰰ　　　　1＀㄀㘀㔀㤀Ⰰ　　　　　㄀㄀㘀㘀　Ⰰ　　　　　㄀661,0000011662,000001166 Ⰰ　　　　　㄀㄀㘀㘀㐀Ⰰ　　　　　㄀㄀㘀㘀㔀Ⰰ　　0011666,0000011667,00000

The table is correct.