Oracle + dbunit throws AmbiguousTableNameException

Question

I'm using DBUnit to populate the database so that its content is a known content during testing.

The db schema I'm working on is in an Oracle 11g instance in which they reside other db schemas. In some of these schemas has been defined a table to which has been associated with a public synonym and on which have been given the rights to select.

When I run the xml that defines how the database must be populated, also if the xml file doesn't contain the table defined in several schemas, DBUnit throws the AmbiguousTableNameException exception on that table.

I found that there are 3 solutions to solve this behavior:

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).

In my case, I can only apply the solution 1, but even if I adopt it, I got the same exception.

The table that gives me problems is defined in 3 schemas and I don't have the opportunity to act on it in any way.

Please, someone could help me?

Abducent

AmbiguousTableNameException might also be thrown if the same table is specified more than once in a DataSet, e.g. new DefaultDataSet(new ITable[] { new DefaultTable("myschema.mytable"), new DefaultTable("myschema.mytable") }); – Darrendarrey

Answer 1

I found the solution: I specified the schema in the name of the tables and I have set to true the property http://www.dbunit.org/features/qualifiedTableNames (corresponding to org.dbunit.database.FEATURE_QUALIFIED_TABLE_NAMES).

By this way, my xml code to populate tables look like:

<?xml version='1.0' encoding='UTF-8'?>
<dataset>
  <SCHEMA.TABLE ID_FIELD="1" />
</dataset>

where SCHEMA is the schema name, TABLE is the table name.

To se the property I've used the following code:

DatabaseConfig dBConfig = dBConn.getConfig(); // dBConn is a IDatabaseConnection
dBConfig.setProperty(DatabaseConfig.FEATURE_QUALIFIED_TABLE_NAMES, true);

Abducent

For Spring JDBC, with the latest MySQL Connector (v8.0.17) I had to additionally also set the following property on Spring's Implementation of javax.sql.DataSource "props.setProperty("databaseTerm", "schema");" – Stallard

Could you please specify how exactly you set the property http://www.dbunit.org/features/qualifiedTableNames to true? – Backwoodsman

Answer 2

In my case,

I granted dba role to user, thus dbunit throw AmbiguousTableNameException.

After I revoke dba role to user, I solve that problem.

SQL> revoke dba from username;

Partan

Thank you. This solved the problem for me. – Deane

Answer 3

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")

Thanks Smitha

Didache

Answer 4

I was using SpringJDBC along with MySQL Connector (v8.0.17). Following the 2 steps explained in this answer alone did not help.

1. First I had to set the schema on the spring datasource.
2. Then I also had to set a property "databaseTerm" to "schema"
   • by default it is set to "catalogue" as explained here.
   • We must set this property because (in Spring's implementation of javax.sql.DataSource) if it's not set (i.e. defaulted to "catalogue") then the connection returned by dataSource.getConnection() will not have the schema set on it even if we had set it on the dataSource.

   @Bean
   public DriverManagerDataSource cloudmcDataSource() {
      DriverManagerDataSource dataSource = new DriverManagerDataSource();
      dataSource.setDriverClassName("<driver>");
      dataSource.setUrl("<url>");
      dataSource.setUsername("<uname>");
      dataSource.setPassword("<password>");
      dataSource.setSchema("<schema_name>");

      Properties props = new Properties();
      // the following key-value pair are constants; must be set as is
      props.setProperty("databaseTerm", "schema");  
      dataSource.setConnectionProperties(props);
      return dataSource;
   }

3. Don't forget to make the changes explained in answer here.

Stallard