Oracle数据库INSERT语句报ORA-00906错误，请求排查原因

你的INSERT语句在DB2正常运行但Oracle报错，核心原因是Oracle与DB2的SQL语法存在差异，以下是具体问题点及解决办法：

1. DB2专用日期字面量格式不被Oracle支持 #

DB2中用{d '2023-05-03'}表示日期的写法，Oracle完全不识别，语法解析时会出现混乱，进而触发"缺少左括号"的错误提示。

• 修复：改用Oracle支持的日期写法，二选一即可：
  • 标准日期字面量：DATE '2023-05-03'
  • TO_DATE函数转换：TO_DATE('2023-05-03', 'YYYY-MM-DD')

2. CAST转换VARCHAR时未指定长度 #

DB2允许CAST(ROW_NUMBER() OVER (...) AS VARCHAR)省略长度，但Oracle要求必须明确指定VARCHAR的长度（比如VARCHAR(20)），否则会引发语法解析错误。

• 修复：将CAST部分修改为CAST(ROW_NUMBER() OVER (ORDER BY ACCOUNTID) AS VARCHAR(20))

3. 自定义函数UB_CONCAT的兼容性问题 #

需要确认Oracle环境中是否存在UB_CONCAT函数，且参数格式与DB2一致。如果该函数是DB2专属，或者Oracle中函数的参数类型/数量定义不符，也会导致解析错误。

• 修复：如果Oracle没有这个函数，可替换为Oracle原生写法：
  • 用||字符串连接符：'20230503184525443' || CAST(ROW_NUMBER() OVER (ORDER BY ACCOUNTID) AS VARCHAR(20))
  • 或用Oracle的CONCAT函数（仅支持两个参数）：CONCAT('20230503184525443', CAST(ROW_NUMBER() OVER (ORDER BY ACCOUNTID) AS VARCHAR(20)))

修改后的完整SQL示例 #

insert into UBTB_ACCOUNTTRANSLIMIT(
  UBACCTTRANSLIMITID,
  UBACCOUNTID,
  UBTRANSACTIONGROUPID,
  UBISACTIVE,
  UBEXPOSUREAMOUNT,
  UBEXPOSURECOUNT,
  UBEXPOSUREREVIEWFREQ,
  UBTRANSACTIONLIMITCOUNT,
  UBTRANSACTIONLIMITAMT,
  UBLIMITEXCESSACTION,
  UBLASTREVIEWDATE,
  UBTRANSACTIONLIMITYPE,
  UBTRANSACTIONLIMITID,
  UBREVIEWMONTH,
  UBREVIEWDAY,
  VERSIONNUM
) 
SELECT UBACCTTRANSLIMITID,
       ACCOUNTID,
       UBTRANSACTIONGROUPID,
       UBISACTIVE,
       UBEXPOSUREAMOUNT,
       UBEXPOSURECOUNT,
       UBEXPOSUREREVIEWFREQ,
       UBTRANSACTIONLIMITCOUNT,
       UBTRANSACTIONLIMITAMT,
       UBLIMITEXCESSACTION,
       UBLASTREVIEWDATE,
       UBTRANSACTIONLIMITYPE,
       UBTRANSACTIONLIMITID,
       UBREVIEWMONTH,
       UBREVIEWDAY,
       VERSIONNUM
FROM   (
  SELECT '20230503184525443' ||
           CAST(ROW_NUMBER() OVER (ORDER BY ACCOUNTID) AS VARCHAR(20))
         as UBACCTTRANSLIMITID,
         ACCOUNTID,
         'Y' AS UBEXPOSUREREVIEWFREQ,
         0 AS UBLIMITEXCESSACTION,
         '0' AS UBTRANSACTIONLIMITYPE,
         'TRANSGROUP1' AS UBTRANSACTIONGROUPID,
         1000.000000 AS UBTRANSACTIONLIMITAMT,
         0 AS UBTRANSACTIONLIMITCOUNT,
         'LIMITJPY1' AS UBTRANSACTIONLIMITID,
         'Y' AS UBISACTIVE,
         DATE '2023-05-03' AS UBLASTREVIEWDATE,
         0 AS UBEXPOSUREAMOUNT,
         0 AS UBEXPOSURECOUNT,
         '01' AS UBREVIEWMONTH,
         '3' AS UBREVIEWDAY,
         0 AS VERSIONNUM
  FROM   ACCOUNT
  WHERE PRODUCTCONTEXTCODE = ?
) VALUELIST

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