Re: Fatal bug in Oracle JDBC OCI driver - Oracle admits it.

Your code will certainly work, but frankly, I consider it a work around just like mine. You are instanciating and distroying the Statement object 1000 times just to overcome the way that the Oracle OCI driver handles an insert. I've been around Oracle long enough to know that they will do nothing about this, and a workaround of some sort will be necessary. I've never been able to understand how Oracle can have such a good database engine, and surround it with such crappy software. For instance, SQLLOADER has been around for years and it's still a junky piece of software. Oracle's attitude has always been, "It works, why are you complaining?" Anyway, I know that it won't change, and I've decided to recommend that the company go with Weblogic's OCI drivers, since I believe that they are much better. I think that Oracle Corporation's attitudes toward its customers developed long ago when there was nothing out there that could compete with the database engine. Times are changing.
Thanks for your time, Thomas

Thomas Kyte wrote:

> A copy of this was sent to "Dean Willis" <dean_at_logical-approach.com>
> (if that email address didn't require changing)
> On Sat, 22 May 1999 18:22:23 GMT, you wrote:
>
> >I've just spent two days checking everything in my system, and multiple
> >calls to oracle technical support to find out that there is a problem in the
> >Oracle JDBC OCI driver that Oracle thinks may or may not be a problem worth
> >fixing. The driver builds a cursor when it does an insert and then fails to
> >release that cursor. In addition to being a waste of resources, when you
> >reach the number of inserts that is equal to your ORACLE MAX_CURSORS, you
> >will abend with an ORA-01000 Max open cursors exceeded. The only workaround
> >for this is to keep track of how many inserts you have made, and before it
> >gets to the MAX_CURSORS limit, then you must disconnect and re-connect to
> >the database. Keep in mind that Oracle and the drivers open cursors that
> >you don't know about, so you are never really sure when you need to
> >disconnect and reconnect to the database. Oracle has asked me to send them
> >all my code, and the abend and everything else they could think of, because
> >even though they know about this bug, they are not sure it is serious enough
> >to fix. If you are only doing a few inserts, then it will only waste your
> >resources, but if you are doing a real application that does many inserts,
> >it will abend on you. I can't believe that Oracle is so arrogant about this
> >to think it not a problem that needs fixing.
> >
> >These are the JDBC OCI drivers on the Oracle CD for Oracle 8.0.5, so I do
> >not know if it affects other versions, but I would suspect so. We are also
> >running Solaris 2.7 with JDK 1.1.
> >
>
> Is it possible that you are calling lots of routines that each declare their own
> "Statement" variables and let these go out of scope without calling .close()?
>
> For example, i think I am able to replicate your issue with the following code.
>
> I have a table T ( x number );
> my open_cursors is set to 1,000
>
> the following code works:
>
> import java.sql.*;
> import oracle.jdbc.driver.*;
>
> class test {
> public static void main (String args []) throws SQLException {
>
> // Load Oracle driver
> DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());
>
> // Connect to the local database
> Connection conn =
> //DriverManager.getConnection ("jdbc:oracle:thin:_at_slackdog:1521:oracle8",
> DriverManager.getConnection ("jdbc:oracle:oci8:_at_slackdog.world",
> "scott", "tiger");
>
> // Query the employee names
> Statement stmt = conn.createStatement ();
>
> stmt.execute( "delete from t" );
>
> for( int i = 0; i <= 2000; i++)
> {
> Statement NewStmt = conn.createStatement ();
>
> NewStmt.execute( "INSERT INTO T VALUES (" + i + ")" );
> NewStmt.close();
> }
> }
> }
>
> but if you comment out the NewStmt.close(); inside the loop, it fails with max
> open cursors after a while. That is because the statement is just going out of
> scope inside the loop for each iteration -- and we have not closed the cursor.
> As there are no destructors in java, we can't clean up cursors automagically --
> java will clean up unused memory but it won't clean up 'everything' possible.
>
> Are you ever closing statements?
>
> still be interested in getting the tar number in any case
>
> See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'...
>
> Thomas Kyte
> tkyte_at_us.oracle.com
> Oracle Service Industries
> Reston, VA USA
>
> --
> Opinions are mine and do not necessarily reflect those of Oracle Corporation
Received on Wed May 26 1999 - 04:23:46 CEST