Oracle兼容H2时XMLAGG排序语法错误的解决方案咨询

生产环境正常运行的Oracle查询 #

select sub.subscriber_id,
RTRIM 
( xmlagg (xmlelement (c, subsrc.source_cd || ',') order by subsrc.source_cd).extract ('//text()') 
, ',' ) AS subscribed_sourceCodes 
from DR_DBA.submodule sub, DR_DBA.submoudle_source subsrc, DR_DBA.source src 
where subsrc.subscriber_id = sub.subscriber_id 
        and subsrc.source_cd = src.source_cd 
        and sub.active_in = 'Y' 
        and src.subscribed_in = 'Y' 
group by  sub.subscriber_id;

注：原查询中sub.subscriber_id与RTRIM之间的逗号为语法必需项，缺失会直接触发H2解析报错。

H2测试环境报错信息 #

Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "select sub.subscriber_id
RTRIM 
( xmlagg (xmlelement (c, subsrc.source_cd || ',') order by subsrc.source_cd).extract ('//text()') 
, ',' ) AS subscribed_sourceCodes 
from DR_DBA.submodule sub, DR_DBA.submoudle_source subsrc, DR_DBA.source src 
where subsrc.subscriber_id = sub.subscriber_id 
        and subsrc.source_cd = src.source_cd 
        and sub.active_in = 'Y' 
        and src.subscribed_in = 'Y' 
group by  sub.subscriber_id;" expected "[, ., ::, AT, FORMAT, *, /, %, +, -, ||, NOT, IS, ILIKE, REGEXP, AND, OR, ,, )"; SQL statement:
select sub.subscriber_id
RTRIM 
( xmlagg (xmlelement (c, subsrc.source_cd || ',') order by subsrc.source_cd).extract ('//text()') 
, ',' ) AS subscribed_sourceCodes 
from DR_DBA.submodule sub, DR_DBA.submoudle_source subsrc, DR_DBA.source src 
where subsrc.subscriber_id = sub.subscriber_id 
        and subsrc.source_cd = src.source_cd 
        and sub.active_in = 'Y' 
        and src.subscribed_in = 'Y' 
group by  sub.subscriber_id;  [42001-220]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:514)
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:489)
    at org.h2.message.DbException.getSyntaxError(DbException.java:261)
    at org.h2.command.Parser.getSyntaxError(Parser.java:910)
    at org.h2.command.Parser.read(Parser.java:5793)
    at org.h2.command.Parser.readIfMore(Parser.java:1312)

已尝试的无效自定义函数 #

原自定义函数存在方法名与功能完全不匹配的问题（如XMLAGG别名指向toDate方法），无法实现Oracle原生函数逻辑：

schema.sql（XMLAGG） #

drop ALIAS if exists XMLAGG;
CREATE ALIAS XMLAGG as '
import java.lang.*;
@CODE
java.lang.String toDate(String s) throws Exception {
  return "";
}
'

schema1.sql（XMLELEMENT） #

drop ALIAS if exists XMLELEMENT;
CREATE ALIAS XMLELEMENT as '
import java.lang.*;
@CODE
java.lang.String toDate(String s, String dateFormat) throws Exception {
  return "";
}
'

schema2.sql（RTRIM） #

drop ALIAS if exists RTRIM;
CREATE ALIAS RTRIM as '
import java.lang.*;
@CODE
java.lang.String toDate(String s, String dateFormat) throws Exception {
  return s;
}
'

H2数据库连接URL #

jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE;MODE=ORACLE;INIT=RUNSCRIPT FROM 'classpath:./schema.sql';RUNSCRIPT FROM 'classpath:./schema1.sql';RUNSCRIPT FROM 'classpath:./schema2.sql'

可行解决方案 #

1. 替换为正确的自定义函数实现 #

替换原有schema文件，实现与Oracle逻辑一致的函数：

自定义XMLAGG聚合函数（schema.sql）

XMLAGG是聚合函数，需用H2的CREATE AGGREGATE语法定义，同时模拟原查询的排序逻辑：

DROP AGGREGATE IF EXISTS XMLAGG;
CREATE AGGREGATE XMLAGG(String xmlElement) RETURNS String
INITIALIZE 'new java.util.ArrayList<String>()'
ADD '((java.util.ArrayList<String>) arg0).add(arg1)'
MERGE '((java.util.ArrayList<String>) arg0).addAll((java.util.ArrayList<String>) arg1)'
FINALIZE 'java.util.Collections.sort((java.util.ArrayList<String>) arg0); StringBuilder sb = new java.lang.StringBuilder(); for(String s : (java.util.ArrayList<String>) arg0) { sb.append(s); } return sb.toString();';

自定义XMLELEMENT函数（schema1.sql）

模拟Oracle的XMLELEMENT生成包含指定内容的XML元素：

DROP ALIAS IF EXISTS XMLELEMENT;
CREATE ALIAS XMLELEMENT AS '
import java.lang.String;
@CODE
String xmlelement(String elementName, String content) {
    return "<" + elementName + ">" + content + "</" + elementName + ">";
}
';

自定义EXTRACT函数（新增schema3.sql）

添加EXTRACT函数提取XML片段中的纯文本：

DROP ALIAS IF EXISTS EXTRACT;
CREATE ALIAS EXTRACT AS '
import java.lang.String;
@CODE
String extract(String xmlContent, String xpath) {
    return xmlContent.replaceAll("<[^>]+>", "");
}
';

自定义RTRIM函数（schema2.sql）

实现Oracle风格的RTRIM，移除字符串末尾指定字符：

DROP ALIAS IF EXISTS RTRIM;
CREATE ALIAS RTRIM AS '
import java.lang.String;
@CODE
String rtrim(String str, String trimChar) {
    if (str == null || trimChar == null || str.isEmpty()) {
        return str;
    }
    int endIdx = str.length();
    while (endIdx > 0 && trimChar.indexOf(str.charAt(endIdx - 1)) != -1) {
        endIdx--;
    }
    return str.substring(0, endIdx);
}
';

2. 调整H2连接URL参数 #

添加Oracle兼容增强参数，并引入新增的schema3.sql：

jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE;MODE=ORACLE;DATABASE_TO_UPPER=FALSE;ALLOW_UNKNOWN_FUNCTIONS=TRUE;INIT=RUNSCRIPT FROM 'classpath:./schema.sql';RUNSCRIPT FROM 'classpath:./schema1.sql';RUNSCRIPT FROM 'classpath:./schema2.sql';RUNSCRIPT FROM 'classpath:./schema3.sql'

参数说明：

• DATABASE_TO_UPPER=FALSE：保留原查询中的表名/列名大小写，避免H2自动转为大写
• ALLOW_UNKNOWN_FUNCTIONS=TRUE：防止未定义函数触发报错（已定义所有必要函数，此为冗余保障）

3. 确认原查询语法正确性 #

确保原查询中sub.subscriber_id与RTRIM之间存在逗号，这是SQL语法的强制要求，缺失会直接导致H2解析报错。

内容的提问来源于stack exchange，提问作者Vinayak Prabhutendolkar