Oracle + dbunit gets AmbiguousTableNameException

Question

I am using dbunit to create database backups, which can be imported and exported. My application can use several database engines: MySQL, PostgreSQL, SQLServer, H2 and Oracle.

All of the above work fine with the following code:

            // Connect to the database 
        conn =BackupManager.getInstance().getConnection();
        IDatabaseConnection connection = new DatabaseConnection(conn);
        InputSource xmlSource = new InputSource(new FileInputStream(new File(nameXML)));
        FlatXmlProducer flatXmlProducer = new FlatXmlProducer(xmlSource);
        flatXmlProducer.setColumnSensing(true);

        DatabaseOperation.CLEAN_INSERT.execute(connection,new FlatXmlDataSet(flatXmlProducer));  

But on Oracle I get this exception:

!ENTRY es.giro.girlabel.backup 1 0 2012-04-11 11:51:40.542
!MESSAGE Start import backup
org.dbunit.database.AmbiguousTableNameException: AQ$_SCHEDULES
    at org.dbunit.dataset.OrderedTableNameMap.add(OrderedTableNameMap.java:198)
    at org.dbunit.database.DatabaseDataSet.initialize(DatabaseDataSet.java:231)
    at org.dbunit.database.DatabaseDataSet.getTableMetaData(DatabaseDataSet.java:281)
    at org.dbunit.operation.DeleteAllOperation.execute(DeleteAllOperation.java:109)
    at org.dbunit.operation.CompositeOperation.execute(CompositeOperation.java:79)
    at es.giro.girlabel.backup.ImportBackup.createData(ImportBackup.java:39)
    at es.giro.girlabel.backup.handlers.Import.execute(Import.java:45)

Dao

Answer 1

From the docs:

public class AmbiguousTableNameException extends DataSetException

This exception is thrown by IDataSet when multiple tables having the same name are accessible. This usually occurs when the database connection have access to multiple schemas containing identical table names.

Possible solutions:

1) Use a database connection credential that has access to only one database schema.

2) Specify a schema name to the DatabaseConnection or DatabaseDataSourceConnection constructor.

3) Enable the qualified table name support (see How-to documentation).

Insalubrious

Thanks, I already solved this issue, but it was by the solution 1 you mentioned. – Pained

I you are using spring-dbunit, solution 1) can be achieved by setting the system property spring.dbunit.schema. – Sapajou

I got it fixed by setting DatabaseConfigBean#setQualifiedTableNames to true. – Saxophone

The solutions are more clearly explained in this post: https://mcmap.net/q/1161544/-oracle-dbunit-throws-ambiguoustablenameexception. For Spring JDBC I had to do this: https://mcmap.net/q/1161544/-oracle-dbunit-throws-ambiguoustablenameexception – Unable

Answer 2

For whom uses SpringDBUnit. I had struggled with this very annoying issue. I had ended up solving the issue by adding the configuration for com.github.springtestdbunit.bean.DatabaseConfigBean and com.github.springtestdbunit.bean.DatabaseDataSourceConnectionFactoryBean.

This is my full spring context for SpringDBUnit

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
        destroy-method="close">
        <property name="driverClassName" value="oracle.jdbc.driver.OracleDriver" />
        <property name="url" value="jdbc:oracle:thin:@localhost:1521/XE" />
        <property name="username" value="xxxx" />
        <property name="password" value="xxxx" />
    </bean>


    <bean id="sessionFactory"
        class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean">
        <property name="dataSource">
            <ref bean="dataSource" />
        </property>
        <property name="hibernateProperties">
            <props>
                <prop key="hibernate.dialect">org.hibernate.dialect.OracleDialect</prop>
                <prop key="hibernate.show_sql">true</prop>
            </props>
        </property>
        <property name="annotatedClasses">
            <list>
                <value>xxx.example.domain.Person</value>
            </list>
        </property>
    </bean>

    <bean id="dbUnitDatabaseConfig" class="com.github.springtestdbunit.bean.DatabaseConfigBean">
        <property name="skipOracleRecyclebinTables" value="true" />
        <property name="qualifiedTableNames" value="true" />
        <!-- <property name="caseSensitiveTableNames" value="true"/> -->
    </bean>
    <bean id="dbUnitDatabaseConnection"
        class="com.github.springtestdbunit.bean.DatabaseDataSourceConnectionFactoryBean">
        <property name="dataSource" ref="dataSource"/>
        <property name="databaseConfig" ref="dbUnitDatabaseConfig" />
        <property name="schema" value="<your_schema_name>"/>
    </bean>

Gouge

Answer 3

Setting the database schema fixed it for me:

@Bean
public DatabaseDataSourceConnectionFactoryBean dbUnitDatabaseConnection(final DataSource dataSource){
    final DatabaseDataSourceConnectionFactoryBean connectionFactory = new DatabaseDataSourceConnectionFactoryBean();
    connectionFactory.setDataSource(dataSource);
    connectionFactory.setSchema(DB_SCHEMA);
    return connectionFactory;
}

Fricative

Answer 4

I had the same AmbiguousTableNameException while executing Dbunits aginst Oracle DB. It was working fine and started throwing error one day.

Rootcause: while calling a stored procedure, it got modified by mistake to lower case. When changed to upper case it stared working.

I could solve this also by setting the shema name to IDatabaseTester like iDatabaseTester.setSchema("SCHEMANAMEINCAPS")

Also please make sure your connection doesn't access only to many schemas having same table name.

Confirm

Answer 5

You might encounter issues when importing data from Hibernate before DBUnit runs. According to the database you are using, the casing of table and column names could be important.

For example, in HSQL, database names must be declared in uppercase. In case you import data via Hibernate's import.sql, make sure the table names are also in uppercase there, otherwise you'll end up with the following problem:

• Hibernate creates the tables in lower case
• DBUnit reads the table names from the DB in lower case
• DBUnit tries to import its datasets using upper case table names
• You end up in a mess, with the ambiguous name exception.

Remember to also check whether multiple tables were created during a previous run (both upper and lower case), in which case you need to clean it up too.

Waylonwayman

Answer 6

For me with Oracle worked simple creation schema with some priveleges instead ALL or DBA. Dbunit stopped conflicting with tables from other schemas.

GRANT CREATE SESSION, CONNECT, RECOURCE TO testdb IDENTIFIED BY testdb;

Medullary