Oracle批量插入SQL报错排查及修正方案咨询

问题背景 #

尝试使用INSERT ALL语句向ITDEV.COM_CATEGORY_TB表批量插入数据，先后两种写法均报错：

第一种写法及报错 #

INSERT ALL INTO ITDEV.COM_CATEGORY_TB (CAT_CODE,CAT_DESCRIPTION,CREATED_ON,CREATED_BY,UPDATED_ON,UPDATED_BY) VALUES
     (1001,'Cost Savings',TIMESTAMP'2010-12-14 00:00:00.0','SESH',NULL,NULL),
     (1002,'Business Improvements',TIMESTAMP'2010-12-14 00:00:00.0','SESH',TIMESTAMP'2011-01-17 09:03:22.0','TEMP1'),
     (1003,'Health and Wellness',TIMESTAMP'2010-12-14 00:00:00.0','SESH',TIMESTAMP'2011-01-21 13:18:23.0','TEMP1'),
     (1004,'Complaints',TIMESTAMP'2010-12-14 00:00:00.0','SESH',TIMESTAMP'2011-01-12 09:34:42.0','SESH'),
     (1005,'Others',TIMESTAMP'2010-12-14 00:00:00.0','SESH',TIMESTAMP'2011-01-12 11:06:43.0','SESH')
    SELECT * FROM dual;

报错信息：

SQL Error [928] [42000]: ORA-00928: missing SELECT keyword

第二种写法及报错 #

INSERT ALL 
INTO ITDEV.COM_CATEGORY_TB (CAT_CODE,CAT_DESCRIPTION,CREATED_ON,CREATED_BY,UPDATED_ON,UPDATED_BY) VALUES
     (1001,'Cost Savings',TIMESTAMP'2010-12-14 00:00:00.0','SESH',NULL,NULL)
INTO ITDEV.COM_CATEGORY_TB (CAT_CODE,CAT_DESCRIPTION,CREATED_ON,CREATED_BY,UPDATED_ON,UPDATED_BY) VALUES
     (1002,'Business Improvements',TIMESTAMP'2010-12-14 00:00:00.0','SESH',TIMESTAMP'2011-01-17 09:03:22.0','TEMP1')
INTO ITDEV.COM_CATEGORY_TB (CAT_CODE,CAT_DESCRIPTION,CREATED_ON,CREATED_BY,UPDATED_ON,UPDATED_BY) VALUES
     (1003,'Health and Wellness',TIMESTAMP'2010-12-14 00:00:00.0','SESH',TIMESTAMP'2011-01-21 13:18:23.0','TEMP1')
INTO ITDEV.COM_CATEGORY_TB (CAT_CODE,CAT_DESCRIPTION,CREATED_ON,CREATED_BY,UPDATED_ON,UPDATED_BY) VALUES
     (1004,'Complaints',TIMESTAMP'2010-12-14 00:00:00.0','SESH',TIMESTAMP'2011-01-12 09:34:42.0','SESH')
INTO ITDEV.COM_CATEGORY_TB (CAT_CODE,CAT_DESCRIPTION,CREATED_ON,CREATED_BY,UPDATED_ON,UPDATED_BY) VALUES
     (1005,'Others',TIMESTAMP'2010-12-14 00:00:00.0','SESH',TIMESTAMP'2011-01-12 11:06:43.0','SESH')
    SELECT * FROM dual;

报错信息：

SQL Error [4091] [42000]: ORA-04091: table ITDEV.COM_CATEGORY_TB is mutating, trigger/function may not see it
ORA-06512: at "ITDEV.COM_CATEGORY_TR", line 4
ORA-04088: error during execution of trigger 'ITDEV.COM_CATEGORY_TR'

错误原因及解决方法 #

1. ORA-00928: missing SELECT keyword #

原因：Oracle的INSERT ALL语法不支持在单个INTO子句后用逗号分隔多条VALUES记录，这种写法属于MySQL等其他数据库的批量插入格式，Oracle不兼容。

解决：

• 可以采用第二种写法，为每条记录单独编写INTO子句；
• 也可以改用INSERT ... SELECT结合UNION ALL构造数据源的方式，示例如下：

INSERT INTO ITDEV.COM_CATEGORY_TB (CAT_CODE,CAT_DESCRIPTION,CREATED_ON,CREATED_BY,UPDATED_ON,UPDATED_BY)
SELECT 1001,'Cost Savings',TIMESTAMP'2010-12-14 00:00:00.0','SESH',NULL,NULL FROM dual
UNION ALL
SELECT 1002,'Business Improvements',TIMESTAMP'2010-12-14 00:00:00.0','SESH',TIMESTAMP'2011-01-17 09:03:22.0','TEMP1' FROM dual
UNION ALL
SELECT 1003,'Health and Wellness',TIMESTAMP'2010-12-14 00:00:00.0','SESH',TIMESTAMP'2011-01-21 13:18:23.0','TEMP1' FROM dual
UNION ALL
SELECT 1004,'Complaints',TIMESTAMP'2010-12-14 00:00:00.0','SESH',TIMESTAMP'2011-01-12 09:34:42.0','SESH' FROM dual
UNION ALL
SELECT 1005,'Others',TIMESTAMP'2010-12-14 00:00:00.0','SESH',TIMESTAMP'2011-01-12 11:06:43.0','SESH' FROM dual;

2. ORA-04091: table is mutating #

原因：表ITDEV.COM_CATEGORY_TB上的触发器ITDEV.COM_CATEGORY_TR在批量插入过程中访问了正在被修改的表（即"变异表"）。Oracle禁止行级触发器在触发语句执行期间读取或修改触发它的表，此时表数据处于不一致状态，INSERT ALL会触发该检查。

解决方法：

• 修改触发器逻辑：如果业务允许，将行级触发器改为语句级触发器；或者使用复合触发器，通过分阶段处理（语句执行前后、行处理阶段）避免直接访问变异表。
• 临时禁用触发器：在批量插入前临时禁用触发器，完成后再启用（需确保业务上不会破坏数据完整性）：

  -- 禁用触发器
  ALTER TRIGGER ITDEV.COM_CATEGORY_TR DISABLE;
  -- 执行批量插入
  INSERT ALL 
  INTO ITDEV.COM_CATEGORY_TB (CAT_CODE,CAT_DESCRIPTION,CREATED_ON,CREATED_BY,UPDATED_ON,UPDATED_BY) VALUES
       (1001,'Cost Savings',TIMESTAMP'2010-12-14 00:00:00.0','SESH',NULL,NULL)
  INTO ITDEV.COM_CATEGORY_TB (CAT_CODE,CAT_DESCRIPTION,CREATED_ON,CREATED_BY,UPDATED_ON,UPDATED_BY) VALUES
       (1002,'Business Improvements',TIMESTAMP'2010-12-14 00:00:00.0','SESH',TIMESTAMP'2011-01-17 09:03:22.0','TEMP1')
  INTO ITDEV.COM_CATEGORY_TB (CAT_CODE,CAT_DESCRIPTION,CREATED_ON,CREATED_BY,UPDATED_ON,UPDATED_BY) VALUES
       (1003,'Health and Wellness',TIMESTAMP'2010-12-14 00:00:00.0','SESH',TIMESTAMP'2011-01-21 13:18:23.0','TEMP1')
  INTO ITDEV.COM_CATEGORY_TB (CAT_CODE,CAT_DESCRIPTION,CREATED_ON,CREATED_BY,UPDATED_ON,UPDATED_BY) VALUES
       (1004,'Complaints',TIMESTAMP'2010-12-14 00:00:00.0','SESH',TIMESTAMP'2011-01-12 09:34:42.0','SESH')
  INTO ITDEV.COM_CATEGORY_TB (CAT_CODE,CAT_DESCRIPTION,CREATED_ON,CREATED_BY,UPDATED_ON,UPDATED_BY) VALUES
       (1005,'Others',TIMESTAMP'2010-12-14 00:00:00.0','SESH',TIMESTAMP'2011-01-12 11:06:43.0','SESH')
      SELECT * FROM dual;
  -- 启用触发器
  ALTER TRIGGER ITDEV.COM_CATEGORY_TR ENABLE;
  

• 改用常规批量插入：使用INSERT ... SELECT（如前面的UNION ALL示例），部分场景下这种写法不会触发变异表错误，具体取决于触发器的逻辑。

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