Monday, 6 June 2011

JUnit, DBUnit and Oracle

As previously blogged, DBUnit is a powerful addition to your unit test armoury. Having used it with Oracle, there are a few nuances which are worth writing about and therefore remembering!

When creating a connection to an Oracle database, DBUnit will populate a Map of table names. By default these will not be prefixed with the schema name. As a result, you can get duplicate table names. An example of an exception raised for the duplicate table WWV_FLOW_DUAL100 is shown below:

org.dbunit.database.AmbiguousTableNameException: WWV_FLOW_DUAL100 at org.dbunit.dataset.OrderedTableNameMap.add(
 at org.dbunit.database.DatabaseDataSet.initialize(
 at org.dbunit.database.DatabaseDataSet.getTableMetaData(
 at org.dbunit.operation.AbstractOperation.getOperationMetaData(
 at org.dbunit.operation.AbstractBatchOperation.execute(

To resolve this, set the database config property FEATURE_QUALIFIED_TABLE_NAMES to be true. This will make sure all tables in the Map are unique. As a consequence of this, the table names in the XML data file will need to be prefixed with the schema name.

Another useful database config property is PROPERTY_DATATYPE_FACTORY. In the XML data file, if there are dates with the time element set then the time element will be ignored unless the database config property PROPERTY_DATATYPE_FACTORY is set to new Oracle10DataTypeFactory() (or the equivalent data factory for the version of Oracle being used.)

An example of setting these values in a @BeforeClass annotated JUnit method is shown below:

public static void loadDataset() throws Exception {

    // database connection
    ResourceCache resourceCache = ResourceCache.getInstance();
    String driverClassString = resourceCache.getProperty("");
    String databaseURL = resourceCache.getProperty("datasource.url");
    String username = resourceCache.getProperty("test.datasource.username");
    String password = resourceCache.getProperty("test.datasource.password");

    Class driverClass = Class.forName(driverClassString);
    Connection jdbcConnection = DriverManager.getConnection(databaseURL, username, password);              
    connection = new DatabaseConnection(jdbcConnection);
    DatabaseConfig config = connection.getConfig();
    config.setProperty(DatabaseConfig.FEATURE_QUALIFIED_TABLE_NAMES, true);
    config.setProperty(DatabaseConfig.PROPERTY_DATATYPE_FACTORY, new Oracle10DataTypeFactory());         
    FlatXmlDataSetBuilder flatXmlDataSetBuilder = new FlatXmlDataSetBuilder();
    dataset =


  1. To Avoid to add full qualified name and allow to use other schema, You can enforce schema name when you create data source, In this case you dont have ambigus problem :

    1. SpringConf
      ..bean class="org.dbunit.database.DatabaseDataSourceConnection"
      constructor-arg ref="dataSource"
      constructor-arg name="schema" type="java.lang.String" value="${schema}"


Note: only a member of this blog may post a comment.