Bug in oracle.sql.ArrayDescriptor?

Question

Today we had a problem with our web application when database was updated but web application was not restarted.
After investigation we found that during db update one user defined type was dropped and created again.
Type was not changed - it was just dropped and created again using the same sql statement as it was before.

Following code can show this problem.
There is java.sql.SQLSyntaxErrorException "ORA-00902: invalid datatype" when second prepared statement is executed.

String str = "select * from MYTESTTABLE where test in (select column_value from TABLE (?) tmp)";

OracleConnection conn = null;
Properties connectionProps = new Properties();
connectionProps.put("user", "user");
connectionProps.put("password", "pwd");

conn = (OracleConnection) DriverManager
        .getConnection("conn", connectionProps);

Statement stmt = conn.createStatement();
stmt.execute("drop table mytesttable");
stmt.execute("create table MYTESTTABLE ( test NUMBER(22))");
System.out.println("Table MYTESTTABLE was created");

stmt.execute("drop type my_type");
System.out.println("Type my_type was dropped");
stmt.execute("create type my_type as table of NUMBER(22)");
System.out.println("Type my_type was created");

String[] northEastRegion = { "10022", "02110", "07399" };

oracle.sql.ARRAY arr1 = new oracle.sql.ARRAY(
        oracle.sql.ArrayDescriptor.createDescriptor("MY_TYPE", conn),
        conn, northEastRegion);

PreparedStatement ps = conn.prepareStatement(str);
ps.setArray(1, arr1);
ps.executeQuery();

stmt.execute("drop type my_type");
System.out.println("Type my_type was dropped");
stmt.execute("create type my_type as table of NUMBER(22)");
System.out.println("Type my_type was created");

oracle.sql.ARRAY arr2 = new oracle.sql.ARRAY(
        oracle.sql.ArrayDescriptor.createDescriptor("MY_TYPE", conn),
        conn, northEastRegion);

ps = conn.prepareStatement(str);
ps.setArray(1, arr2);
ps.executeQuery(); //java.sql.SQLSyntaxErrorException: ORA-00902: invalid datatype

Why arr1 and arr2 are treated as different types?
As I understand the problem is that these types have different OID.
But in java code I work with these types only by name. I do not use OID.

How to deal with this problem?
Should we always restart web application after update of DB? Even if structure of DB was not changed.
Or should we specify(hardcode) OID when we create MY_TYPE?
What is the correct way?

UPDATE:
It seems to me that I've found the answer and a bug in oracle.sql.ArrayDescriptor.
See my answer below.

Volodymyr Bezuglyy · Accepted Answer · 2015-09-30 08:52:39Z

oracle.sql.ArrayDescriptor uses cache.
By default the second instance of the same ArrayDescriptor will be loaded from cache.
And it will be different than type in database, because type in database was re-created.

There is a constructor in ArrayDescriptor in which you can specify whether to replace any cached descriptor.

static ArrayDescriptor  createDescriptor(java.lang.String name, java.sql.Connection conn, boolean recurse, boolean force)
static ArrayDescriptor createDescriptor(SQLName sqlName, java.sql.Connection conn, boolean recurse, boolean force)

But there is a bug in the constructor where first parameter is String - force parameter is not used in the body of that constructor.
It is used only in the body of constructor with SQLName.
You can see code for example here.

So, to fix our problem we can use the constructor with SQLName and force parameter set to true. In this case cache will not be used.

String[] northEastRegion = { "10022", "02110", "07399" };
String str = "select * from MYTESTTABLE where test in (select column_value from TABLE (?) tmp)";

OracleConnection conn = null;
Properties connectionProps = new Properties();
connectionProps.put("user", "user");
connectionProps.put("password", "pwd");

conn = (OracleConnection) DriverManager
                .getConnection("connstr", connectionProps);

Statement stmt = conn.createStatement();

stmt.execute("drop table mytesttable");
stmt.execute("create table MYTESTTABLE ( test NUMBER(22))");

stmt.execute("drop type my_type1");
stmt.execute("create type my_type1 as table of NUMBER(22)");

ArrayDescriptor desc1 = oracle.sql.ArrayDescriptor.createDescriptor("MY_TYPE1", conn);

stmt.execute("drop type my_type1");
stmt.execute("create type my_type1 as table of NUMBER(22)");

oracle.sql.SQLName sqlName = new oracle.sql.SQLName("MY_TYPE", conn);
ArrayDescriptor desc2 = oracle.sql.ArrayDescriptor.createDescriptor(sqlName, conn, true, true);
oracle.sql.ARRAY arr2 = new oracle.sql.ARRAY(desc2, conn, northEastRegion);

PreparedStatement ps = conn.prepareStatement(str);
ps.setArray(1, arr2);
ps.executeQuery();

Good finding. I didn't have time to really test it until now. I used ODAC and .NET which uses different approach than ArrayDescriptor and your case works there without any issue. I know that Oracle tries to recompile stuff to seamlessly handle changes happening in metadata but even after DROP/CREATE type I didn't see any invalidation of the cursor.

Husqvik · Accepted Answer · 2015-09-29 17:35:18Z

1

I think the issue here is the prepared statement with combination with data type used as a row source. Prepared statement effectively creates and optimizes execution plan and when executed cursor is created and the address of the cursor is stored so next time you execute the same statement with different bind variable you just execute the existing cursor again (to avoid both hard and soft parsing). When you drop the object Oracle invalidates everything that depends on this object, including cursors. If you issue the same statement including parsing (not prepared) it should produce new execution plan involving the new datatype but as prepared you want to re-execute cursor that is not valid anymore.

answered Sep 29, 2015 at 17:35

Husqvik

5,8191 gold badge23 silver badges32 bronze badges

Collectives™ on Stack Overflow

Bug in oracle.sql.ArrayDescriptor?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related