Using DBTools.J with the Oracle Driver: Objects Are Not Getting Released Correctly

Article ID: 1069
Last updated: 03 Aug, 2009
Article ID: 1069
Last updated: 03 Aug, 2009
Revision: 1
Views: 3212
Posted: 24 Jun, 1998
by Dean J.
Updated: 03 Aug, 2009
by Rehme L.

Problem



When I create more than 50 Selector objects (or other DML object) using DBTools.J and the Oracle driver, I get the following error:

 

   ORA-01000: maximum open cursors exceeded


Cause



This is a known problem in Oracle's JDBC driver that gets propagated through DBTools.J. The following Java program will eventually throw an exception when executed, dependent on how many open cursors are allowed in the Oracle database:

    for (int i = 1; i<300; i++) {
        Connection cn2 = pool.getConnection();
        DBTable test = new DBTable("test3");
        Selector sel = new Selector();
        sel.addToSelectList(test.getColumn("f1"));
        sel.addToSelectList(test.getColumn("f2"));
        Reader rdr = sel.getReader ( cn2 ); 
        cn2.returnToPool(); 
    }


Action



The workaround to the Oracle driver is simple: each Statement is closed when it is no longer needed. This is actually recommended by Sun in their Java Series reference:

"It is recommended that Statement objects be closed explicitly when they are no longer needed, thereby freeing DBMS resources as soon as possible."

To implement this advice through DBTools.J, add the following line into the for loop:

    for (int i = 1; i<300; i++) {
        Connection cn2 = pool.getConnection();
        DBTable test = new DBTable("test3");
        Selector sel = new Selector();
        sel.addToSelectList(test.getColumn("f1"));
        sel.addToSelectList(test.getColumn("f2"));
        Reader rdr = sel.getReader ( cn2 );
        sel.close();         cn2.returnToPool();      } 
This article was:   Helpful | Not helpful
Report an issue
Article ID: 1069
Last updated: 03 Aug, 2009
Revision: 1
Views: 3212
Posted: 24 Jun, 1998 by Dean J.
Updated: 03 Aug, 2009 by Rehme L.

Others in this category