Aggregare function for HSQL to emulate Oracle's WM_CONCAT

HSQL.java

    @SuppressWarnings("unused")
    public static String wmConcat(String in, Boolean flag,
                                  String[] register, Integer[] counter) {
        if (flag) {
            if (register[0] == null) {
                return null;
            }
            return register[0];
        }
        if (in == null) {
            return null;
        }
        if (register[0] == null) {
            register[0] = in;
            counter[0] = 1;
        } else {
            register[0] += "," + in;
            counter[0]++;
        }
        return null;
    }

wm_concat.sql

CREATE AGGREGATE FUNCTION wm_concat(
  IN val VARCHAR(255), IN flag BOOLEAN,
  INOUT register VARCHAR(255), INOUT counter INT)
RETURNS VARCHAR(512)
  NO SQL
  LANGUAGE JAVA
  EXTERNAL NAME 'CLASSPATH:uk.ac.ebi.gxa.dao.AtlasDAOTestCase.wmConcat'

HSQLDB 2.0+ supports user-defined aggregate functions. In case you're using Oracle for production and stick to WM_CONCAT for whatever reason, use this function, it'll help you maintaining your test suite one week longer. Anyway, you're almost always better off not using Oracle-specific SQL at all...

Please note the stacktraces are not too useful if an exception is thrown by a custom function. Log everything or, better, use a debugger

For further details, study http://hsqldb.org/doc/2.0/guide/sqlroutines-chapt.html