Managing Oracle stored procedures in go

Viewed 35

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.

0 Answers
Related