H2 Oracle兼容模式执行MERGE语句报未知数据类型OBJECT_KEY异常

在MODE=Oracle配置的H2数据库中执行MERGE SQL时抛出异常，同一条SQL在原生Oracle数据库中可正常运行，需要实现单段SQL同时兼容Oracle与H2数据库。

报错信息 #

org.h2.jdbc.JdbcSQLNonTransientException: Unknown data type:
"OBJECT_KEY"; SQL statement:
MERGE INTO TEST_OBJECTS obj USING
(select ? as OBJECT_KEY, ? as OBJECT_VALUE, ? as ETAG, ? as
CREATED_BY, SYSTIMESTAMP as CREATION_DATE, ? as LAST_UPDATED_BY,
SYSTIMESTAMP as LAST_UPDATE_DATE from dual) tmp ON (obj.OBJECT_KEY =
tmp.OBJECT_KEY) WHEN MATCHED THEN UPDATE SET
obj.OBJECT_VALUE=tmp.OBJECT_VALUE, obj.TAG=tmp.TAG,
obj.LAST_UPDATED_BY=tmp.LAST_UPDATED_BY,
obj.LAST_UPDATE_DATE=SYSTIMESTAMP WHEN NOT MATCHED THEN INSERT
(OBJECT_KEY, OBJECT_VALUE, TAG, CREATED_BY, CREATION_DATE,
LAST_UPDATED_BY, LAST_UPDATE_DATE) VALUES (tmp.OBJECT_KEY,
tmp.OBJECT_VALUE, tmp.TAG ,tmp.CREATED_BY, tmp.CREATION_DATE,
tmp.LAST_UPDATED_BY, tmp.LAST_UPDATE_DATE)

原始执行SQL #

MERGE INTO TEST_OBJECTS obj USING (select ? as OBJECT_KEY, ? as
OBJECT_VALUE, ? as TAG, ? as CREATED_BY, SYSTIMESTAMP as
CREATION_DATE, ? as LAST_UPDATED_BY, SYSTIMESTAMP as LAST_UPDATE_DATE
from dual) tmp ON (obj.OBJECT_KEY = tmp.OBJECT_KEY) WHEN MATCHED THEN
UPDATE SET obj.OBJECT_VALUE=tmp.OBJECT_VALUE,
obj.TAG=tmp.TAG,obj.LAST_UPDATED_BY=tmp.LAST_UPDATED_BY,
obj.LAST_UPDATE_DATE=SYSTIMESTAMP WHEN NOT MATCHED THEN INSERT
(OBJECT_KEY, OBJECT_VALUE, TAG, CREATED_BY, CREATION_DATE,
LAST_UPDATED_BY, LAST_UPDATE_DATE)VALUES (tmp.OBJECT_KEY,
tmp.OBJECT_VALUE, tmp.TAG ,tmp.CREATED_BY,tmp.CREATION_DATE,
tmp.LAST_UPDATED_BY, tmp.LAST_UPDATE_DATE)

H2 Oracle兼容模式下解析from dual子查询中的未绑定类型参数占位符?时，无法自动推导参数对应的数据类型，会将占位符后的别名误识别为数据类型声明，最终抛出Unknown data type: "OBJECT_KEY"异常。另外报错日志中子查询使用了ETAG作为别名，和后续引用、插入字段的TAG名称不一致，也会触发后续执行错误。

• 优先使用显式类型转换方案：为子查询中所有参数占位符添加CAST类型转换，明确参数类型，该语法在Oracle和H2中均原生支持，无版本兼容问题。修改后的SQL示例如下，CAST的目标类型需要和TEST_OBJECTS表对应字段的实际定义保持一致：

MERGE INTO TEST_OBJECTS obj 
USING (
    select 
        CAST(? AS VARCHAR2(255)) as OBJECT_KEY, 
        CAST(? AS CLOB) as OBJECT_VALUE, 
        CAST(? AS VARCHAR2(100)) as TAG, 
        CAST(? AS VARCHAR2(100)) as CREATED_BY, 
        SYSTIMESTAMP as CREATION_DATE, 
        CAST(? AS VARCHAR2(100)) as LAST_UPDATED_BY, 
        SYSTIMESTAMP as LAST_UPDATE_DATE
    from dual
) tmp 
ON (obj.OBJECT_KEY = tmp.OBJECT_KEY) 
WHEN MATCHED THEN UPDATE SET
    obj.OBJECT_VALUE=tmp.OBJECT_VALUE, 
    obj.TAG=tmp.TAG,
    obj.LAST_UPDATED_BY=tmp.LAST_UPDATED_BY,
    obj.LAST_UPDATE_DATE=SYSTIMESTAMP 
WHEN NOT MATCHED THEN INSERT 
    (OBJECT_KEY, OBJECT_VALUE, TAG, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE)
VALUES 
    (tmp.OBJECT_KEY, tmp.OBJECT_VALUE, tmp.TAG ,tmp.CREATED_BY,tmp.CREATION_DATE, tmp.LAST_UPDATED_BY, tmp.LAST_UPDATE_DATE)

• 备选适配方案：若使用H2 1.4.200及以上版本，可在H2 JDBC连接串末尾追加参数;NON_KEYWORDS=OBJECT_KEY,OBJECT_VALUE,TAG,CREATED_BY,LAST_UPDATED_BY，将用到的字段别名标记为非关键字，避免H2语法解析误判。该方案需要维护所有用到的别名列表，适配成本高于显式类型转换。

内容的提问来源于stack exchange，提问作者santosh jk