Next: ORA-01002: fetch out of sequence

[AndreFK]

I get this error when calling a stored procedure, all arguments are string and I expect the procedure to return a result set with the values I'm sending as input.

Anyone could point out what am I doing wrong?

So far, the only answers I have found online (like starting a transaction) haven't helped.

   db, err := sql.Open("godror", connstr)
   tx, _ := db.Begin()

if err != nil {
	WriteLog("Connection Error: "+err.Error(), true)
	tx.Rollback()
	return contacts
}

stmt, err := db.Prepare("begin QUALTRICS.qualtrics_data_pkg.fetch_email_survey_data(:1,:2,:3,:4); end;")

if err != nil {
	WriteLog("Statement Error: "+err.Error(), true)
	tx.Rollback()
	return contacts
}

defer stmt.Close()
brand, survey := TouchpointToBrandAndSurveyType(touchpoint)

WriteLog("Loading contacts from DB inserted on date "+date+" with touchpoint "+touchpoint+" ...", false)

var out driver.Rows

_, e := stmt.ExecContext(ctx, date, brand, survey, sql.Out{Dest: &out})

if e != nil {
	WriteLog("ExecContext Error: "+e.Error(), true)
	tx.Rollback()
	return contacts
}

rows, err := godror.WrapRows(ctx, db, out)
if err != nil {
	fmt.Println("WrapRows Error: ", err.Error())
	tx.Rollback()
	return contacts
}
defer rows.Close()

var id sql.NullInt64
var nil_FirstName, nil_LastName, nil_Email, nil_Country, nil_Brand, nil_BookNumber, nil_Touchpoint, nil_Cat, nil_EmployeeName sql.NullString

if rows.Next() {
	fmt.Println(rows.Scan(&nil_FirstName, &nil_LastName, &nil_Email, &nil_Country, &nil_Brand, &nil_BookNumber, &nil_Touchpoint, &nil_Cat, &nil_EmployeeName, &id))
}
defer tx.Commit()

[tgulacsi]

What do you use tx for?
You have to use the same connection for the result and the WrapRows:

db, err := sql.Open("godror", connstr)
if err != nil {
	WriteLog("Connection Error: "+err.Error(), true)
	tx.Rollback()
	return contacts
}
tx, err := db.BeginTx(ctx, nil)
if err != nil {
	WriteLog("Connection Error: "+err.Error(), true)
	return contacts
}
defer tx.Rollback()

stmt, err := tx.PrepareContext(ctx, "begin QUALTRICS.qualtrics_data_pkg.fetch_email_survey_data(:1,:2,:3,:4); end;")
if err != nil {
	WriteLog("Statement Error: "+err.Error(), true)
	return contacts
}
defer stmt.Close()

brand, survey := TouchpointToBrandAndSurveyType(touchpoint)
WriteLog("Loading contacts from DB inserted on date "+date+" with touchpoint "+touchpoint+" ...", false)

var out driver.Rows
_, e := stmt.ExecContext(ctx, date, brand, survey, sql.Out{Dest: &out})
if e != nil {
	WriteLog("ExecContext Error: "+e.Error(), true)
	return contacts
}

rows, err := godror.WrapRows(ctx, tx, out)
if err != nil {
	fmt.Println("WrapRows Error: ", err.Error())
	return contacts
}
defer rows.Close()

var id sql.NullInt64
var nil_FirstName, nil_LastName, nil_Email, nil_Country, nil_Brand, nil_BookNumber, nil_Touchpoint, nil_Cat, nil_EmployeeName sql.NullString

if rows.Next() {
	fmt.Println(rows.Scan(&nil_FirstName, &nil_LastName, &nil_Email, &nil_Country, &nil_Brand, &nil_BookNumber, &nil_Touchpoint, &nil_Cat, &nil_EmployeeName, &id))
}
tx.Commit()

[AndreFK]

I had the transaction there because I tried connecting through a transaction, but that didn't solve the problem. I copied and pasted the code in your reply but I still get Next: ORA-01002: fetch out of sequence.

By debugging I found out that on line 333 of the file rows.go I get thrown the error.

 err := r.statement.checkExecNoLOT(func() C.int {
   return C.dpiStmt_fetchRows(r.dpiStmt, maxRows, &r.bufferRowIndex, &r.fetched, &moreRows)
 })

I'm not sure what this function does, but something I'm doing in my code is making this function return and error.

I get the error once it gets in C.dpiStmt_fetchRows() when I call rows.Next().

Here is the call stack in case it is of any help:

github.com/godror/godror._Cfunc_dpiStmt_fetchRows (_cgo_gotypes.go:4651)
github.com/godror/godror.(*rows).Next.func2.1 (/Users/afigueroa/go/pkg/mod/github.com/godror/godror@v0.29.0/rows.go:334)
github.com/godror/godror.(*rows).Next.func2 (/Users/afigueroa/go/pkg/mod/github.com/godror/godror@v0.29.0/rows.go:334)
github.com/godror/godror.(*drv).checkExecNoLOT (/Users/afigueroa/go/pkg/mod/github.com/godror/godror@v0.29.0/drv.go:262)
github.com/godror/godror.(*conn).checkExecNoLOT (/Users/afigueroa/go/pkg/mod/github.com/godror/godror@v0.29.0/conn.go:84)
github.com/godror/godror.(*rows).Next (/Users/afigueroa/go/pkg/mod/github.com/godror/godror@v0.29.0/rows.go:333)
database/sql.(*Rows).nextLocked (/usr/local/go/src/database/sql/sql.go:2967)
database/sql.(*Rows).Next.func1 (/usr/local/go/src/database/sql/sql.go:2945)
database/sql.withLock (/usr/local/go/src/database/sql/sql.go:3396)
database/sql.(*Rows).Next (/usr/local/go/src/database/sql/sql.go:2944)
github.com/AndreFK/survey_qualtrics/internal/qualtrics.(*Client).LoadContactsFromDB 
(/Users/afigueroa/Documents/Qualtrics/internal/qualtrics/client.go:255)
github.com/AndreFK/survey_qualtrics.TestGetFromDB (/Users/afigueroa/Documents/Qualtrics/client_test.go:15)
testing.tRunner (/usr/local/go/src/testing/testing.go:1259)
testing.(*T).Run·dwrap·21 (/usr/local/go/src/testing/testing.go:1306)
runtime.goexit (/usr/local/go/src/runtime/asm_amd64.s:1581)

Could it be cursor gets closed when I try to fetch the data? Is there a way for me to check the cursor's status?

[tgulacsi]

What is QUALTRICS.qualtrics_data_pkg.fetch_email_survey_data doing?
Does it ROLLBACK somewhere?

Can it be iterated successfully in PL/SQL?

DECLARE
  v_cur SYS_REFCURSOR;
  v_num PLS_INTEGER := 0;
begin 
  QUALTRICS.qualtrics_data_pkg.fetch_email_survey_data(:1,:2,:3,v_cur);
  LOOP
    FETCH v_cur;
    EXIT WHEN v_cur%NOTFOUND;
    v_num := v_num + 1;
  END LOOP;
  OPEN v_cur FOR
    SELECT v_num FROM DUAL;
  :4 := v_cur;
END;

?

[AndreFK]

This is what it does

 PROCEDURE fetch_email_survey_data 
 (
 pi_date          IN varchar2 ,
 pi_brand         IN varchar2,
 pi_survey_type   IN varchar2,
 po_survey_result OUT QUALTRICS_survey_data_typ
 ) AS
BEGIN
    OPEN    po_survey_result FOR
    SELECT  FIRST_NAME
              , LAST_NAME
	      , EMAIL_ADDRESS
              , COUNTRY
	      , BRAND
	      , BOOK_NO
	      , SURVEY_TYPE
	      , RESORT_NAME
	      , AGENT_NAME
             , AGENT_ID 
    FROM    
            QUALTRICS.QUALTRICS_survey_data
    WHERE   
            run_date              =   pi_date
    AND
            brand                 =   pi_brand
    AND
		    survey_type           =   pi_survey_type
    ORDER BY 
            qualtrics_data_id;

 EXCEPTION
 WHEN OTHERS
 THEN
     QUALTRICS_DATA_PKG.insert_err_msg('GET_QUALTRICS_SURVEY_DATA','Error-'|| sqlerrm);
 END fetch_email_survey_data;

It is a simple select from.

[tgulacsi]

godror.WrapRows WORKS. Try it with a

SELECT * FROM all_objects

query.
So your function does errors out.

Your function should rethrow the exception, and does not go on silently! - "WHEN OTHERS THEN ...; RAISE;"