#374 Unable to insert row with Oracle XMLType

[Michal Šída]

Hallo,

I am using a version 2.5.1 of dbUnit and I have a problem with inserting rows into table with XMLTYPE column. In the case, that I am not specify any value for this column (so NULL should be inserted into this column) this exception will be thrown during data insertion:

java.sql.SQLSyntaxErrorException: ORA-00932: inconsistent datatypes: expected - got CLOB

But if I specify a value for this column (even empty byte array), the inserting will go well.

I have an idea, that problem is in implementation of method org.dbunit.database.statement.IPreparedBatchStatement#addValue in classes org.dbunit.database.statement.PreparedBatchStatement and org.dbunit.database.statement.SimplePreparedStatement. There is a call:

_statement.setNull(++_index, dataType.getSqlType());

but it is possible, that something like

_statement.setNull(++_index, oracle.jdbc.OracleTypes.OPAQUE, "SYS.XMLTYPE");

is needed, this solution is used e.g. in http://www.tzehon.com/2011/06/28/oracle-xmltype-in-ibatismybatis/

Michal

[Michal Šída]

Btw. I have actually a problem with inserting not empty XMLTYPE too. No exception is thrown, but there is empty value in DB column after importing data set. Hmm. Strange...

[Jeff Jensen]

Thanks for the report.
Did you find a fix or a workaround? We'd need to make a cross-DB change (non-Oracle specific) in that spot, or make a special case for Oracle to override. Please make a test and patch if you are interested.

[Michal Šída]

I prepared a fix for both problems. It allows NULL value for column with XMLTYPE and it correctly load a value into this column. I add some unit tests for both scenarios and modify the implementation of OracleXMLTypeDataType. The tests works correctly for me and I tried the compiled dbUnit library in our project and the failing tests are passing sucessfully now.

I attach the fixing patch, you can look on my solution. It is possible, that you will need some additional libraries, I am actually using ojdbc6-12.1.0.2.jar, xdb6-12.1.0.2.jar, xmlparserv2_2.jar and for non-unicode uasge is needed orai18n.jar. The libraries come from Oracle's sites and repositories.

[Jeff Jensen]

Thank you for the patch!
Patch applied cleanly. You put in a lot of work to resolve this one, well done!

Please test the updated snapshot and reply with your results!

[Michal Šída]

I tried snapshot version dbunit-2.5.3-20160522.043317-16.jar and it fixed both my problematic scenarios. Thanks for patch including.

[Jeff Jensen]

Great, thank you for confirming!

[Jeff Jensen]

Hi Michal, if you have a moment, I would appreciate your thoughts on a new issue cause by these changes [#400]. I'm wondering if you can determine a way to implement this one without causing [#400].

[Michal Šída]

Hi, I supposed, that three parameter of setNull is safe to call with null value in third parameter and it will be ignored by the jdbc driver (see specification ).

But if it causes some problems it looks safe to change the implementation of PreparedBatchStatement.java and SimplePreparedStatement.java, that they will call 2-parameter variant in case that dataType.getSqlTypeName() returns null. The 3-paramater variant will be used only for non-null dataType.getSqlTypeName() return value - and the only case is org.dbunit.ext.oracle.OracleXMLTypeDataType implementation now and it is not used with the problematic JDBC driver.

[Michal Šída]

There is a simple workarround. I do not use dbUnit on my actual project, so I can not test it, could you run some tests and try it in some real project, please?

[Jeff Jensen]

Thank you Michal for the fix!
Committed.