I am trying to load 300 million records from the stored procedure cursor. (Gordor + sql) "database/sql" + "github.com/godror/godror"
Making sql rows
func (o *OracleDb) OpenCursorTX(tx *sql.Tx, sqlStmt string) (*sql.Rows, error) {
var cur driver.Rows
ctx := context.Background()
stmt, err := tx.PrepareContext(ctx, sqlStmt)
if err != nil {
logrus.Fatalf("error parsing cursor %s: %s", sqlStmt, err.Error())
return nil, err
}
if _, err := stmt.ExecContext(ctx, sql.Out{Dest: &cur}, godror.PrefetchCount(100000), godror.FetchArraySize(100000)); err != nil {
logrus.Fatalf("error exec cursor %s: %s", sqlStmt, err.Error())
return nil, err
}
rows, err := godror.WrapRows(ctx, tx, cur)
if err != nil {
logrus.Fatalf("error cursor wrap rows %s: %s", sqlStmt, err.Error())
return nil, err
}
if err := stmt.Close(); err != nil {
return nil, err
}
return rows, nil
}
Reading rows in code
rows, err := l.oracleDb.OpenCursorTX(tx, "begin drs_router_read.get_rate_b_groups(po_rate_b_groups => :po_rate_b_groups); end;")
if err != nil {
return err
}
var rbg domain.RmsGroupHist
var gwgrId, direction int
var dialCode, key, rbgDBegin, rbgDEnd string
rbgMap := make(map[string][]domain.RmsGroupHist)
i:=0
for rows.Next() {
if err := rows.Scan(&gwgrId, &direction, &dialCode, &rbg.RmsgId, &rbgDBegin, &rbgDEnd); err != nil {
return fmt.Errorf("error scan db rows loadRateBGroups %v", err)
}
rbg.DBegin = util.StrToInt64(rbgDBegin)
rbg.DEnd = util.StrToInt64(rbgDEnd)
key = domain.RBObjectKey + ":" + strconv.Itoa(gwgrId) + ":" + strconv.Itoa(direction) + ":" + dialCode
rbgMap[key] = append(rbgMap[key], rbg)
i++
if i%100000 == 0 {
logrus.Infof("rows %d\n", i)
}
}
rows.Next() hangs for 17 minutes after that len(rbgMap) = 0
I think I'm missing something, it works fine on 9 million.