Oracle + dbunit gets AmbiguousTableNameException value

Question

I use dbunit to create database backups that can be imported and exported. My application can use several database mechanisms: MySQL, PostgreSQL, SQLServer, H2 and Oracle.

All of the above works 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 in 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) 

Answer 1

From docs :

public class AmbiguousTableNameExceptionextends DataSetException

This exception is thrown by IDataSet when multiple tables that have the same name are available. This usually happens when a database connection has access to multiple schemas containing an identical table of names.

Possible solutions: 1) Use database connection credentials that access only one database schema. 2) Specify the schema name for DatabaseConnection or DatabaseDataSourceConnection. 3) Enable qualified table name support (see the documentation for the documentation).

Answer 2

For whom uses SpringDBUnit. I struggled with this very annoying problem. I decided to solve the problem 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> 

Answer 3

Setting up 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; } 

Answer 4

I had the same AmbiguousTableNameException while executing a Dbunits aginst Oracle DB. He worked fine and started throwing a mistake one day.

Rootcause: when calling a stored procedure, it was changed by mistake to lowercase. When he was changed to uppercase, he looked at work.

I could solve this by setting the shema name to IDatabaseTester, for example iDatabaseTester.setSchema ("SCHEMANAMEINCAPS")

Also, make sure that your connection does not have access only to many schemas that have the same table name.

Answer 5

There may be problems loading data from Hibernate before starting DBUnit. According to the database you are using, the shell of table and column names may be important.

For example, in HSQL, database names must be uppercase. If you import data through Hibernate import.sql, make sure that the table names are also uppercase, otherwise you will have the following problem:

• Hibernate creates lowercase tables
• DBUnit reads table names from a database in lower case
• DBUnit tries to import its datasets using uppercase table names
• You get into a mess with an undefined exception.

Remember to also check to see if several tables were created during the previous run (both in upper and lower case), in which case you also need to clear it.