Can't reuse statement when selecting a blob value

[protamail]

When reusing a prepared statement which selects a blob, I'm getting an "ORA-01002: fetch out of sequence" error.
This happens only when selecting a blob column, even when no rows returned. Other select types work OK.
Here's an example program I use. Notice how the repeating query() call results in the error (no error if stmt is closed after each query()):

package main

import (
    "database/sql"
    "log"

    _ "github.com/sijms/go-ora/v2"
)

func main() {
    assertOK := func(err error) {
        if err != nil {
            log.Panic(err)
        }
    }
    db, err := sql.Open("oracle", "oracle://...")
    assertOK(err)
    db.Exec(`create table go_ora_test(a_blob blob)`)
    db.Exec(`insert into go_ora_test values(hextoraw('555555'))`)
    stmt, err := db.Prepare("select a_blob from go_ora_test")
    assertOK(err)

    query := func() {
        rows, err := stmt.Query()
        assertOK(err)
        defer rows.Close()
        var blob []byte
        for rows.Next() {
            err = rows.Scan(&blob)
            assertOK(err)
        }
    }
    query()
    query()
}

The error output:

$ go run main.go
2024/05/15 20:28:21 ORA-01002: fetch out of sequence
panic: ORA-01002: fetch out of sequence


goroutine 1 [running]:
log.Panic({0xc000033e98?, 0x79f0d0?, 0x15c7b80?})
        /home/common/go/src/log/log.go:432 +0x5a
main.main.func1(...)
        /tmp/go_ora_test/main.go:13
main.main.func2()
        /tmp/go_ora_test/main.go:25 +0x70
main.main()
        /tmp/go_ora_test/main.go:34 +0x166
exit status 2

[protamail]

I found it being suggested to append "?lob fetch=post" to connect string to allow for more than 32K blob sizes to be fetched. Interestingly, adding that seems to solve the above issue as well. Still not clear how this works, is there a way to deal with it more explicitly?

[sijms]

starting from v2.8.8 the return size for inline lobs up to 1GB
I will review the code for inline lobs and see if it can be modified to accept multiple calling for query

[sijms]

would you please test the following:

package main

import (
	"database/sql"
	"fmt"
	_ "github.com/sijms/go-ora/v2"
	"os"
)

func main() {
	db, err := sql.Open("oracle", os.Getenv("DSN"))
	if err != nil {
		fmt.Println("can't open db: ", err)
		return
	}
	defer func() {
		err = db.Close()
		if err != nil {
			fmt.Println("can't close db: ", err)
		}
	}()

	stmt, err := db.Prepare("SELECT TO_CLOB('this is a test') FROM DUAL")
	if err != nil {
		fmt.Println("can't prepare stmt: ", err)
		return
	}
	defer func() {
		err = stmt.Close()
		if err != nil {
			fmt.Println("can't close stmt: ", err)
		}
	}()
	_, err = stmt.Query()
	if err != nil {
		fmt.Println("can't query #1: ", err)
		return
	}
	_, err = stmt.Query()
	if err != nil {
		fmt.Println("can't query #2: ", err)
		return
	}
}

you should not close the rows only close the stmt

[protamail]

I tested the code against v2.8.19 and it works. But only if query results are not being fetched. As soon as I add the following after the 1st query:

var clob string
for rows.Next() {
    err = rows.Scan(&clob)
    if err != nil {
        fmt.Println(err)
    }
}

The next query still reports:
ORA-01002: fetch out of sequence

P.S. My app is a long running service where I prepare, cache, and reuse statements indefinitely. So in my case closing rows after each query is important to prevent memory leaks.

[sijms]

fixed in v2.8.21