Hana转Oracle遇ORA-30076错误，求DATEUPD字段正确转换方案

Oracle中DATE类型无法直接使用EXTRACT(HOUR/MINUTE/SECOND FROM ...)，因为这些提取函数仅支持TIMESTAMP类型。要获取DATE字段的时分秒部分，最简便高效的方式是使用TO_CHAR函数直接格式化。

修正后的完整SQL脚本 #

SELECT 
    P2_RULE,
    P2_ORIGIN,
    Rule_def.DATEUPD as P2_DATE,
    -- 用TO_CHAR直接格式化时分秒，替代原有的EXTRACT拼接逻辑
    TO_CHAR(Rule_def.DATEUPD, 'HH24:MI:SS') AS P2_DATETIME,
    Rule_def.USERUPD || ' | ' || DESC_UTENTE as P2_USER
FROM (
    SELECT 
        P2_RULE,
        P2_ORIGIN,
        DATEUPD,
        USERUPD,
        ROW_NUMBER() over (partition by P2_RULE, p2_origin order by DATEUPD desc) as N_row
    FROM AW_PII_P20018_000001
    WHERE dateupd is not null) Rule_def
JOIN UTENTE
ON Rule_def.USERUPD = COD_UTENTE    
WHERE N_ROW = 1

备选方案（若坚持使用EXTRACT） #

如果一定要保留提取拼接的逻辑，需要先将DATE字段转换为TIMESTAMP类型：

substr('0' || EXTRACT(HOUR FROM CAST(Rule_def.DATEUPD AS TIMESTAMP)), 2) 
|| ':' || 
substr('0' || EXTRACT(MINUTE FROM CAST(Rule_def.DATEUPD AS TIMESTAMP)), 2)
|| ':' || 
substr('0' || EXTRACT(SECOND FROM CAST(Rule_def.DATEUPD AS TIMESTAMP)), 2) AS P2_DATETIME

但此写法冗余且效率低于TO_CHAR，不推荐使用。

内容的提问来源于stack exchange，提问作者Cristina Biagiotti