Cursors are supported.  My guess as to your problem (since you didn't 
send a full code example of what you are doing) is that you need to 
issue multiple 'fetch' statements.  Each fetch statement will return its 
own result set.  So when you have used up the results from one fetch 
statement, you need to execute another.  I have provided an example 
below which I have hacked from some of my own code.


     PreparedStatement l_stmt = null;
     PreparedStatement l_stmtFetch = null;
     PreparedStatement l_stmtClose = null;
     ResultSet l_rset = null;
     try { //exception
       try { //finally
         //open the cursor
         l_stmt = m_dbcon.prepareStatement("DECLARE FOO CURSOR FOR 

         l_stmtFetch = m_dbcon.prepareStatement("FETCH FORWARD 10 FROM 
         while (true) {
           //perform a fetch from the cursor (possibly multiple fetches 
will be done)
           l_rset = l_stmtFetch.executeQuery();
           l_rows = 0;
           while ( {
             String l_bar = l_rset.getString(1);
             //do something useful with the data

           l_rset = null;

           if (l_rows == 0) {
             //no more rows, so we are done

         //don't forget to close the cursor
         l_stmtClose = m_dbcon.prepareStatement("CLOSE FOO");
       } finally {
         if (l_rset != null) {
         if (l_stmt != null) {
         if (l_stmtFetch != null) {
         if (l_stmtClose != null) {
     } catch (SQLException l_se) {
       //do something useful here


> Hello,
> I am retrieveing a lot of data from a table and it results in a
> java.lang.OutOfMemoryError and so I figured I need to make sure I don;t get all
> the data from the database back in one go.
> I have tried setting a cursorName and this does indeed only return the first
> part of the data into the ResultSet. However I can not seem to find a way to
> retrieve the remaining part of the data. I have tried using the following
> command :-
>     if(select.getMoreResults())
>      rs = select.getResultSet();
> However there are never are never any more ResultsSets avaliable.
> Am I doing something wrong or are cursors not implemented in this version of the
> JDBC drivers (jdbc7.1-1.2.jar)?
> An obvious bodge for me (as the data in the database is not changing on a
> regular basis) is to use LIMIT in the SELECT command, however I would rather do
> it a nicer way than that!
> Regards
> Ben
> *******************************************************************************
> Important. This E-mail is intended for the above named person and may be
> confidential and/or legally privileged. If this has come to you in error you
> must take no action based on it, nor must you copy or show it to anyone; please
> inform the sender immediately.
> *******************************************************************************
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Reply via email to