dpiStmt_fetch ignores `DPI-1018: array size of 10 is too small` error

[felipenoris]

If I create a variable with a small buffer size and execute dpiStmt_fetch, the result is found = false even if there are rows resulting from the query.

If I use dpiStmt_fetchRows instead, it throws an error: DPI-1018: array size of 10 is too small .

If then I increase the variable's buffer size, dpiStmt_fetch correctly returns found = true.

Maybe dpiStmt_fetch should also throw an error if the user passes a variable with small buffer size.

[anthony-tuininga]

Can you provide the code that demonstrates the issue?

[felipenoris]

I have this julia code, which can be used to understand the situation. If you need C code, I could work on that later.

This example defines a variable with 10 elements of buffer capacity (maxArraySize). It is defined to the first position of the query statement. After fetch, the result returns found = false.

# will call dpiConn_newVar with maxArraySize = 10
ora_var = Oracle.OraVariable(conn, Oracle.ORA_ORACLE_TYPE_NATIVE_DOUBLE, Oracle.ORA_NATIVE_TYPE_DOUBLE, buffer_capacity=10)

Oracle.execute!(conn, "INSERT INTO TB_VARIABLES ( FLT ) VALUES ( 123.45 )")
Oracle.execute!(conn, "INSERT INTO TB_VARIABLES ( FLT ) VALUES ( 456.78 )")
Oracle.execute!(conn, "INSERT INTO TB_VARIABLES ( FLT ) VALUES ( null )")
stmt = Oracle.Stmt(conn, "SELECT FLT FROM TB_VARIABLES")
Oracle.execute!(stmt)

# will call dpiStmt_define
Oracle.define(stmt, 1, ora_var)

# will call dpiStmt_fetch
fetch_result = Oracle.fetch!(stmt)
println(fetch_result)

The result is shown below, and informs that: found = false and buffer_row_index = 0

Oracle.FetchResult(false, 0x00000000)

Then I set up the same example, but using fetchRows instead of fetch.

ora_var = Oracle.OraVariable(conn, Oracle.ORA_ORACLE_TYPE_NATIVE_DOUBLE, Oracle.ORA_NATIVE_TYPE_DOUBLE, buffer_capacity=10)

Oracle.execute!(conn, "INSERT INTO TB_VARIABLES ( FLT ) VALUES ( 123.45 )")
Oracle.execute!(conn, "INSERT INTO TB_VARIABLES ( FLT ) VALUES ( 456.78 )")
Oracle.execute!(conn, "INSERT INTO TB_VARIABLES ( FLT ) VALUES ( null )")
stmt = Oracle.Stmt(conn, "SELECT FLT FROM TB_VARIABLES")
Oracle.execute!(stmt)

Oracle.define(stmt, 1, ora_var)

# will call dpiStmt_fetchRows with maxRows = 10
fetch_result = Oracle.fetch_rows!(stmt, 10)

When running the last line of code (fetchRows), it throws an exception:

DPI-1018: array size of 10 is too small

[anthony-tuininga]

That is helpful, even though I don't know Julia. :-)

You should get the same error (DPI-1018: array size of 10 is too small) when you call dpiStmt_fetch(), just like when you call dpiStmt_fetchRows(). Perhaps you aren't checking for the error response in that case? In any case, the solution is to do one of the following two things:

(1) create the variable with the default buffer size of 100 (2) call dpiStmt_setFetchArraySize() to adjust the number of rows fetched from the database at one time

[felipenoris]

Oh, yes. There was a bug in my code. I wasn't checking the error code in this case only. Sorry about that!

Thanks!

[anthony-tuininga]

Glad to hear you got it figured out. :-)