Oracle 19c批量插入多行报错问题及解决咨询

使用Oracle 19c批量插入多行数据时，遇到两个典型报错：参考官方文档的WITH子句插入示例，扩展数据后出现ORA-00918: 列定义模糊；参考其他示例时，又出现ORA-00923: 未找到预期的FROM关键字。最终通过INSERT ALL和直接UNION ALL的方式成功插入，修正原代码后报错方法也能正常运行，以下是具体报错原因：

一、ORA-00918: 列定义模糊的原因 #

报错的WITH子句代码 #

INSERT INTO people (id, name, status, phone) 
  WITH names AS (
    SELECT 1, 'John', 1, '214-444-1234'    FROM dual UNION ALL 
    SELECT 2, 'John', 1, '214-444-1234'    FROM dual UNION ALL 
    SELECT 3, 'Mary', 0, '555-111-1234'    FROM dual UNION ALL 
    SELECT 4, 'Mary', 0, '555-111-1234'    FROM dual UNION ALL 
    SELECT 5, 'Jeff', 0, '214-222-1234'    FROM dual UNION ALL 
    SELECT 6, 'Jeff', 0, '214-222-1234'    FROM dual UNION ALL 
    SELECT 7, 'Bill', 1, '817-333-1234'    FROM dual UNION ALL 
    SELECT 8, 'Bill', 1, '817-333-1234'    FROM dual UNION ALL 
    SELECT 9, 'Bob' , 1, '214-555-1234'    FROM dual UNION ALL 
    SELECT 10, 'Bob' , 1, '214-555-1234'    FROM dual UNION ALL 
    SELECT 4, 'Ruth',     1,      'Mrs'    FROM dual UNION ALL 
    SELECT 5, 'Isabelle', 2,      'Miss'   FROM dual UNION ALL 
    SELECT 6, 'Justin',   3,     'Master' FROM dual UNION ALL 
    SELECT 7, 'Lisa',     4,      'Dr'     FROM dual 
  ) 
  SELECT * FROM names;

报错原因 #

当WITH子句中的子查询没有显式指定列别名时，Oracle会自动为列分配默认别名（如COL1、COL2）。但示例中多次出现重复的数值（比如SELECT 4出现两次），Oracle在推导临时表names的列结构时，会将这些重复数值误判为列别名，导致列定义冲突，最终抛出“列定义模糊”的错误。

小数据量时未报错，是因为没有出现重复数值作为隐式列别名的冲突，Oracle能正常推导列结构；扩展数据后出现重复值，触发了该问题。

修正方案 #

在WITH子句的第一个SELECT中显式指定列别名，明确临时表的列结构：

INSERT INTO people (id, name, status, phone) 
  WITH names AS (
    SELECT 1 id, 'jOHN' name, 1 status, '214-444-1234' phone     FROM dual UNION ALL
    SELECT 2, 'John', 1, '214-444-1234'    FROM dual UNION ALL 
    SELECT 3, 'Mary', 0, '555-111-1234'    FROM dual UNION ALL 
    SELECT 4, 'Mary', 0, '555-111-1234'    FROM dual UNION ALL 
    SELECT 5, 'Jeff', 0, '214-222-1234'    FROM dual UNION ALL 
    SELECT 6, 'Jeff', 0, '214-222-1234'    FROM dual UNION ALL 
    SELECT 7, 'Bill', 1, '817-333-1234'    FROM dual UNION ALL 
    SELECT 8, 'Bill', 1, '817-333-1234'    FROM dual UNION ALL 
    SELECT 9, 'Bob' , 1, '214-555-1234'    FROM dual UNION ALL 
    SELECT 10, 'Bob' , 1, '214-555-1234'    FROM dual UNION ALL 
    SELECT 11, 'Ruth',     1,      'Mrs'    FROM dual UNION ALL 
    SELECT 12, 'Isabelle', 2,      'Miss'   FROM dual UNION ALL 
    SELECT 13, 'Justin',   3,     'Master' FROM dual UNION ALL 
    SELECT 14, 'Lisa',     4,      'Dr'     FROM dual 
  ) 
  SELECT * FROM names;

二、ORA-00923: 未找到预期的FROM关键字的原因 #

报错的UNION ALL代码 #

INSERT INTO people ( id, name, status, phone)
SELECT 1 'John', 1, '214-444-1234' FROM DUAL UNION ALL
SELECT 2, 'John', 1, '214-444-1234' FROM DUAL;

报错原因 #

第一个SELECT语句中，1 'John'的写法缺少逗号，Oracle会将'John'解析为1的列别名，导致SELECT列表的列数变为3个（别名是John的列、1、'214-444-1234'），与INSERT目标表的4个列不匹配。同时语法错误导致Oracle无法正确识别FROM关键字的位置，从而抛出该错误。

修正方案 #

在数值和字符串之间添加逗号，保证列数匹配、语法正确：

INSERT INTO people ( id, name, status, phone)
SELECT 1, 'John', 1, '214-444-1234' FROM DUAL UNION ALL
SELECT 2, 'John', 1, '214-444-1234' FROM DUAL;

三、可直接运行的批量插入方法 #

方法1：使用INSERT ALL #

INSERT ALL
    INTO people VALUES (1, 'John', 1, '214-444-1234')
    INTO people VALUES (2, 'John', 1, '214-444-1234')
 SELECT * FROM DUAL;

方法2：直接使用UNION ALL子查询 #

INSERT INTO people
  SELECT 3, 'Mary', 0, '555-111-1234' FROM DUAL
  UNION ALL
  SELECT 4, 'Mary', 0, '555-111-1234' FROM DUAL;

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