Search code examples
javajdbcjdbc-odbc

how to setFetchSize on a cursor

How to set fetch size on a cursor returned by stored procedure. Please find my below code snippet:

conn.setAutoCommit(false);
cstmt = (CallableStatement) conn.createStatement();
cstmt = conn.prepareCall(sqlQuery);
cstmt.execute();
rst = (ResultSet) cstmt.getObject(sqlQuery);
rst.setFetchSize(100);

but resultset seems to ignore setFetchSize. Can anyone suggest me solution? also my oracle version is ojdbc11.2.0

Solution

  • You need to set autocommit as false,

    conObj.setAutoCommit(false);

    And load the callable statement, CallableStatement cstmt = connObj.createStatement();

    From Docs,

    In the case of statements returning a ResultSet, the statement completes when the last row of the ResultSet has been retrieved or when the ResultSet has been closed. In more complex cases, a single statement can return multiple results as well as output parameter values. Here, the COMMIT occurs when all results and output parameter values have been retrieved.