Bearded Magnum

or how to lose your time…

To test my Hibernate mappings, I’ve been inspired by the base unit test shipped with Hibernate. I adapted it to my own needs but the concept remains unchanged: some DBUnit operations are executed before and/or after every unit tests. For instance, tables can be fully recreated and filled with test dataset.

Everything were working fine for weeks when suddenly an error poped out:

org.dbunit.database.AmbiguousTableNameException:TEST.BIN$OgO5GiZBrIrgQAB/AQEh6w==$0

First I considered myself as suspect #1 and look for malicious changes in my code. In vain, I didn’t edit a single line in my testing framework.
A few minutes of googling later, I found out that this is a known DBUnit issue when running on Oracle 10g.

Actually since version 10g, Oracle database comes with a Recycle Bin and the recycled tables are listed by the Oracle JDBC driver. And DBUnit validations steps fail on this unusual table names.

The solution is to purge the recyclebin and then disable it:

sqlplus sys/password as sysdba
SQL> purge dba_recyclebin;
SQL> alter system set recyclebin = OFF scope=both;

I didn’t spend enough time on Google to know if the listing of recycled table is considered as bug or not. In my opinion it should be, because you can’t even execute a select query on these tables and recycle bin management is a distinct process with specific commands etc.

I wrote a quick test case if you want some hands-on experience. Simply compile and execute the class pasted below. Your JDBC driver must be in the classpath. The database settings must be set as system properties.
The expected output is similar too: TEST:BIN$OgO5GiZBrIrgQAB/AQEh6w==$0

public class OracleRecycleBinTest {

    private String schema;
    private Connection conn;

    public void openConnection() throws SQLException, ClassNotFoundException {
        //-Duser=test -Dpassword=test -Dschema=test -Durl=jdbc:oracle:thin:@localhost:1521:XE
        Class.forName("oracle.jdbc.driver.OracleDriver");
        String url = System.getProperty("url");
        String user = System.getProperty("user");
        String password = System.getProperty("password");
        schema = System.getProperty("schema");
        schema = schema != null ? schema.toUpperCase() : schema;
        conn = DriverManager.getConnection(url, user, password);
    }

    public void closeConnection() throws SQLException {
        if (conn != null && !conn.isClosed()) {
            conn.close();
        }
    }

    public void enableRecycleBin() throws SQLException {
        String query = "alter system set recyclebin = ON scope=memory";
        execQuery(query);
    }

    public void createTable() throws SQLException {
        String query = "create table addresses (id number(19,0) not null, city varchar2(64 char), primary key (id))";
        execQuery(query);
    }

    public void dropTable() throws SQLException {
        String query = "drop table addresses";
        execQuery(query);
    }

    private void execQuery(String query) throws SQLException {
        Statement stmt = conn.createStatement();
        try {
            stmt.execute(query);
        } finally {
            stmt.close();
        }
    }

    public void printTableInfo() throws SQLException {
        ResultSet rs = conn.getMetaData().getTables(null, schema, "%", new String[]{"TABLE"});
        try {
            while (rs.next()) {
                String tableName = rs.getString("TABLE_NAME");
                String tableSchema = rs.getString("TABLE_SCHEM");
                System.out.println(tableSchema + ":" + tableName);
            }
        } finally {
            rs.close();
        }

    }

    public void run() {
        try {
            openConnection();
            try {
                enableRecycleBin();
                createTable();
                dropTable();
                printTableInfo();
            } finally {
                closeConnection();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
        new OracleRecycleBinTest().run();
    }
}