Steve's Blog Technical Information on Oracle Middleware.

The following is a simple example that uses 'useFetchSizeWithLongColumn' on a long column where a the row prefetch is set to 15.

I confirm it returns 15 rows on each trip to the database by tracing the session and checking the trc file on the database.

My code looks at follows:

--------------------------------------------------------------------------------------------------------

package project1;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import oracle.jdbc.OracleDriver;

public class TestFetchSizeWithLongColumn {

    private Connection conn = null;
    private Statement stmt = null;
    private Statement altstmt = null;
    private ResultSet rset = null;

    final String traceSQL = 
        "select c.value || '/' || d.instance_name || '_ora_' || " + 
        "to_char(a.spid, 'fm99999') || '.trc' " + 
        "from v$process a, v$session b, v$parameter c, v$instance d " + 
        "where a.addr = b.paddr " + "and b.audsid = userenv('sessionid') " + 
        "and c.name = 'user_dump_dest'";

    public static void main(String[] args) throws SQLException {
        new TestFetchSizeWithLongColumn().run();
    }

    public void run() {
        try {

            Connection conn = getConnection();

            altstmt = conn.createStatement();
            stmt = conn.createStatement();

            altstmt.execute("alter session set events '10046 trace name " + 
                            "context  forever, level 12'");
            ResultSet rset = 
                stmt.executeQuery("select search_condition,rownum" + 
                                  "from dba_constraints");
            altstmt.execute("alter session set events '10046 trace " + 
                            "name context off'");
            displayTraceFileName(conn);
            //            while (rset.next())
            //                System.out.println(rset.getInt(2));            

        } catch (SQLException sqle) {
            //sqle.printStackTrace();
            System.out.println("sqlexception");
        } finally {
            try {
                if (rset != null)
                    rset.close();
                if (stmt != null)
                    stmt.close();
                if (altstmt != null)
                    altstmt.close();
                if (conn != null)
                    conn.close();
            } catch (Exception ex) {
                ex.printStackTrace();
                System.out.println("fatal error");
            }
        }
    }

    public static Connection getConnection() throws SQLException {
        String url = 
            "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)" + 
            "(HOST=sracanov-au2.au.oracle.com)(PORT=1522))" + 
            "(CONNECT_DATA=(SERVICE_NAME=orcl)))";
        java.util.Properties props = new java.util.Properties();

        props.setProperty("user", "system");
        props.setProperty("password", "welcome1");
        props.setProperty("defaultRowPrefetch", "15");
        props.setProperty("useFetchSizeWithLongColumn", "true");

        DriverManager.registerDriver(new OracleDriver());
        Connection conn = DriverManager.getConnection(url, props);
        return conn;
    }

    public void displayTraceFileName(Connection conn) throws SQLException {
        Statement stmt = conn.createStatement();
        ResultSet rset = stmt.executeQuery(traceSQL);
        rset.next();
        String fileName = rset.getString(1);
        System.out.println("TRACE FILE NAME : " + fileName);
    }
}

--------------------------------------------------------------------------------------------------------

Now run the class as follows:

[stever@STEVER-8500 C]$ java -version
java version "1.5.0_06"
Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_06-b05)
Java HotSpot(TM) Client VM (build 1.5.0_06-b05, mixed mode)

[stever@STEVER-8500 C]$ javac -cp .;"D:\My Contents\My Software\Oracle\JDBC Drivers\10.2.0.4\ojdbc14.jar" TestFetchSizeWithLongColumn.java

[stever@STEVER-8500 C]$ java -cp .;"D:\My Contents\My Software\Oracle\JDBC Drivers\10.2.0.4\ojdbc14.jar" TestFetchSizeWithLongColumn
TRACE FILE NAME : /u01/programs/oracle/product/10.2.0/db_1/admin/orcl/udump/orcl_ora_7002.trc

[stever@STEVER-8500 C]$

The result returned is the location of my trace file on the database.

Upon checking the trace file, the following is shown:

=====================
PARSING IN CURSOR #1 len=51 dep=0 uid=5 oct=3 lid=5 tim=1221750605502677 hv=1856978345 ad='2bdf40f8'
select search_condition,rownum from dba_constraints
END OF STMT
PARSE #1:c=0,e=108,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1221750605502672
BINDS #1:
EXEC #1:c=0,e=98,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1221750605502850
WAIT #1: nam='SQL*Net message to client' ela= 2 driver id=1952673792 #bytes=1 p3=0 obj#=-1 tim=1221750605502898
FETCH #1:c=232965,e=227064,p=0,cr=7679,cu=0,mis=0,r=15,dep=0,og=1,tim=1221750605730008
WAIT #1: nam='SQL*Net message from client' ela= 77131 driver id=1952673792 #bytes=1 p3=0 obj#=-1 tim=1221750605807896
=====================

The line:

FETCH #1:c=232965,e=227064,p=0,cr=7679,cu=0,mis=0,r=15,dep=0,og=1,tim=1221750605730008

Contains 'r=15'. 15 rows are prefetched.