Working with go-ora refcursor, sometimes I’m getting can't query RefCursor: driver: bad connection
I use shared connectin like:
func main() {
// Establish a connection to the Oracle database
dsn := "oracle://username:password@hostname:port/service_name"
db, err := sql.Open("oracle", dsn)
if err != nil {
log.Fatalf("Failed to connect to the database: %v", err)
}
defer db.Close() // Ensure the database connection pool is closed when main exits
// Reuse the same connection pool to call a stored procedure with REF CURSOR
for i := 0; i < 5; i++ {
if err := callStoredProcedure(db); err != nil {
log.Printf("Failed to call stored procedure: %v", err)
}
}
}
func callStoredProcedure(db *sql.DB) error {
// Create a variable to hold the REF CURSOR (sql.Rows)
var refCursor *sql.Rows
// Execute the stored procedure
stmt, err := db.Prepare(query)
if err != nil {
return fmt.Errorf("failed to prepare statement: %w", err)
}
defer stmt.Close() // Ensure the statement is closed after execution
// Execute the statement and obtain the REF CURSOR
_, err = stmt.Exec(&refCursor)
if err != nil {
return fmt.Errorf("failed to execute statement: %w", err)
}
defer refCursor.Close() // Ensure the REF CURSOR is closed after processing
// Process the rows returned by the REF CURSOR
for refCursor.Next() {
}
return nil
}
I wonder is it ok to reuse the same db, err := sql.Open(“oracle”, dsn), shoud I create it for every request maybe since I use refcursor?