JDBC - Resultset data processing : Strange behaviour : default fetchsize returned

Question

I have a oracle(10.2) PLSQL procedure which fetches 15 records from a table in a sysrefcursor. I then pass this cursor to a java class as a resultset. This java class is loaded to oracle.

Driver name : Oracle JDBC driver Driver Version : 10.2.0.1.0 Driver Major Version : 10 Driver Minor Version : 2

Observations:

1 Inside the java class, when I iterate through the resultset I get only the first 10 records.

2 If the cursor fetched (20 or more records) or (10 or less) I could get all the records while iterating the resultset.

3 I found that the default fetchsize for the resultset is 10. If I change the fetchSize to 5, and the cursor fetches 8 records, I could get the first 5 records while iterating the resultset.

4 If the cursor fetched (10 or more records) or (5 or less) I could get all the records while iterating the resultset.

5 If I change the resultset fetchSize to 1, I could get all the records in the resultset no matter how many records are fetched by the cursor.

Why is the resultset behaving weirdly?

public static BLOB createZip(BLOB prevBlob, String outPrefix, ResultSet entries, ResultSet rs, Long[] resultRows) throws Exception
{
    OracleConnection conn = null;
    BLOB retBLOB = null;
    int page   = 1;
    int curRow = 0;
    long totalRows = 0;
    try
    {
        conn = (OracleConnection) new OracleDriver().defaultConnection();
        ArrayList entryList = loadEntries(entries);
        retBLOB = BLOB.createTemporary(conn, true, BLOB.DURATION_SESSION);
        retBLOB.open(BLOB.MODE_READWRITE);
        OutputStream bOut = retBLOB.setBinaryStream(0L);
        ZipOutputStream zipOut = new ZipOutputStream(bOut);
        PrintStream out = new PrintStream(zipOut);
        zipOut.putNextEntry(new ZipEntry(outPrefix + "-p" + page + ".csv"));
        writeHeader(out, entryList);
        while (rs.next())
        {
            curRow++;
            totalRows++;
            if (curRow >= maxPageSize)
            {
                zipOut.closeEntry();
                page++;
                zipOut.putNextEntry(new ZipEntry(outPrefix + "-p" + page + ".csv"));
                writeHeader(out, entryList);
                curRow = 0;
            }
            for (int i = 0; i < entryList.size(); i++)
            {
                Entry e = (Entry) entryList.get(i);
                if (i != 0)
                {
                    out.print(",");
                }
                if (e.isEscape())
                    out.print("\"" + escapeExcel(rs.getString(e.getColumn())) + "\"");
                else
                    out.print("\"" + emptyExcel(rs.getString(e.getColumn())) + "\"");
            }
            out.println();
        }

        if (totalRows == 0)
        {
            out.println("\"No Entries Found\"");
        }
        resultRows[0] = new Long(totalRows);
        out.flush();
        zipOut.closeEntry();
        if (prevBlob != null)
        {
            byte[] buf = new byte[1024];
            InputStream bIn = prevBlob.binaryStreamValue();
            ZipInputStream zipIn = new ZipInputStream(bIn);

            ZipEntry inEntry = zipIn.getNextEntry();
            while (inEntry != null)
            {
                zipOut.putNextEntry(new ZipEntry(inEntry.getName()));
                int len;
                while ((len = zipIn.read(buf)) > 0) {
                    out.write(buf, 0, len);
                }
                inEntry = zipIn.getNextEntry();
            }

            zipIn.close();

            try
            {
                prevBlob.freeTemporary();
            }
            catch (SQLException e) { }

        }

        zipOut.close();
        retBLOB.close();
        return retBLOB;
    }
    catch (Exception sex)
    {
        if (retBLOB != null)
        {
            try
            {
                retBLOB.freeTemporary();
            }
            catch (SQLException e) { }
        }
        throw sex;
    }
    finally
    {

        try { entries.close(); } catch (SQLException sex) { }
        try { rs.close(); } catch (SQLException sex) { }
        try
        {
            if (conn != null || !conn.isClosed())
            {
                conn.close();
            }
        }
        catch (SQLException ex)
        {
            ex.printStackTrace();
        }

    }

}

Answer 1

Assuming there is no mistake on your part. I suggest you should check the maxRows of your Statement, in this case. The doc says,

Sets the limit for the maximum number of rows that any ResultSet object generated by this Statement object can contain to the given number. If the limit is exceeded, the excess rows are silently dropped.

maxRows = 0 means there is no limit. So, I suppose this is good to go in your case.

Answer 2

There is a workaround. I fetched the column index before while(rs.next()). The below snippet works absolutely fine for me. But I still fail to understand why resultSet.getString(columnName); failed inside while(rs.next()).

    ArrayList entryList = loadEntries(entries);
    int[] colIndx = new int[entryList.size()];
    ResultSetMetaData rsmd = rs.getMetaData();
    int numberOfColumns = rsmd.getColumnCount();
    for (int i = 0; i < entryList.size(); i++){
       Entry e = (Entry) entryList.get(i);
       for (int j = 1; j <= numberOfColumns; j++){
          if(rsmd.getColumnName(j).equalsIgnoreCase(e.getColumn()))
              colIndx[i] = j;
       }
    }
    try{
        while (rs.next()){
            for (int i = 0; i < colIndx.length ; i++){
                System.out.println("Column Values["+colIndx[i]+"] : "+rs.getString(colIndx[i]));
            }
        }
    }