sijms
RefCursor.Query() invalidates connection if cursor was not initialized by stored procedure
When using go-ora.RefCursor, if a stored procedure does not assign a value to the cursor (e.g., returns NULL or leaves it uninitialized), calling RefCursor.Query() leads to an Oracle error (e.g., ORA-01001: invalid cursor, ORA-24338, etc). After this, the connection becomes invalid and gets returned to the pool, which causes future failures.
This behavior causes hard-to-debug errors in production.
// Assume stored procedure might return NULL or uninitialized REF CURSOR var rset go_ora.RefCursor
_, err := conn.ExecContext(ctx, begin my_proc(:1, :2); end;,
inputParam,
go_ora.Out{Dest: &rset}, // not initialized inside proc
)
if err != nil {
log.Fatal(err)
}
ds, err := rset.Query() // <-- this line triggers OCI error and breaks connection
As a result:
The connection becomes invalid and cannot be reused for further queries.
But it is still returned to the connection pool (*sql.DB).
On the next use of this connection, other unrelated queries begin to fail with confusing errors.
This behavior is very difficult to debug and causes instability in production.
Thank you for fixing this issue! I’ve upgraded to version 2.9.0 and now I’m seeing ORA-01001. However, the session is returned to the pool and stays valid, so it can still be used — that’s great. Thanks again, @sijms !
I’ve encountered another issue now: when a procedure returns OUT parameters in addition to an empty cursor, the other OUT parameters are not being populated if the cursor is empty.
Is it possible to handle this case so that the other parameters are still scanned correctly even if the cursor is empty?
@sijms
In my procedure, I return several OUT parameters along with a REF CURSOR (see code snippet below). Previously, when the REF CURSOR was empty, the other OUT parameters (ClientName, InfoForOldProducts, ErrorCode, ErrorDescription) were still populated correctly.
Now, if the REF CURSOR is empty, the other OUT parameters are not populated at all.
Here is an example of how I call stmt.ExecContext:
` var ( response credit.AdvancedPreCheckResponse refCursor go_ora.RefCursor )
_, err = stmt.ExecContext(ctx, phone, go_ora.Out{Dest: &response.ClientName, Size: 255}, go_ora.Out{Dest: &refCursor}, go_ora.Out{Dest: &response.InfoForOldProducts, Size: 255}, go_ora.Out{Dest: &response.ErrorCode, Size: 255}, go_ora.Out{Dest: &response.ErrorDescription, Size: 255}, ) `