使用单INSERT语句批量插入Oracle数据报错ORA-00933的咨询

问题原因 #

你当前的批量INSERT写法不符合Oracle SQL语法规范，Oracle无法识别在第一个VALUES子句后直接跟无分隔符括号列表的写法，因此触发ORA-00933: SQL命令未正确结束错误。

正确的批量插入写法 #

方法1：标准多行VALUES语法（Oracle 12c R1及以上适用，你的21c版本支持） #

在VALUES后用逗号分隔多个值列表即可：

insert into customers values 
(100356,'ramo',89638527410,to_date('13-04-2023','dd-mm-yyyy'),'ramo@gmail.com','Mexico'),
(100356,'ramo',89638527417,to_date('13-04-2023','dd-mm-yyyy'),'ramo@gmail.com','Finland'),
(100356,'ramo',89638527418,to_date('13-04-2023','dd-mm-yyyy'),'ramo@gmail.com','Ireland'),
(100356,'ramo',89638527419,to_date('13-04-2023','dd-mm-yyyy'),'ramo@gmail.com','Newjersey'),
(100356,'ramo',89638527414,to_date('13-04-2023','dd-mm-yyyy'),'ramo@gmail.com','rome'),
(100356,'ramo',89638527415,to_date('13-04-2023','dd-mm-yyyy'),'ramo@gmail.com','canada'),
(100356,'ramo',89638527416,to_date('13-04-2023','dd-mm-yyyy'),'ramo@gmail.com','Namabia'),
(100356,'ramo',89638527411,to_date('13-04-2023','dd-mm-yyyy'),'ramo@gmail.com','SouthAfrica'),
(100356,'ramo',89638527413,to_date('13-04-2023','dd-mm-yyyy'),'ramo@gmail.com','England'),
(100356,'ramo',89638527415,to_date('13-04-2023','dd-mm-yyyy'),'ramo@gmail.com','Ontario'),
(100356,'ramo',89638527416,to_date('13-04-2023','dd-mm-yyyy'),'ramo@gmail.com','London'),
(100356,'ramo',89638527414,to_date('13-04-2023','dd-mm-yyyy'),'ramo@gmail.com','Mexica');

方法2：INSERT ALL语法（兼容所有Oracle版本） #

若需兼容旧版本Oracle，可使用INSERT ALL语句：

INSERT ALL
INTO customers VALUES (100356,'ramo',89638527410,to_date('13-04-2023','dd-mm-yyyy'),'ramo@gmail.com','Mexico')
INTO customers VALUES (100356,'ramo',89638527417,to_date('13-04-2023','dd-mm-yyyy'),'ramo@gmail.com','Finland')
INTO customers VALUES (100356,'ramo',89638527418,to_date('13-04-2023','dd-mm-yyyy'),'ramo@gmail.com','Ireland')
INTO customers VALUES (100356,'ramo',89638527419,to_date('13-04-2023','dd-mm-yyyy'),'ramo@gmail.com','Newjersey')
INTO customers VALUES (100356,'ramo',89638527414,to_date('13-04-2023','dd-mm-yyyy'),'ramo@gmail.com','rome')
INTO customers VALUES (100356,'ramo',89638527415,to_date('13-04-2023','dd-mm-yyyy'),'ramo@gmail.com','canada')
INTO customers VALUES (100356,'ramo',89638527416,to_date('13-04-2023','dd-mm-yyyy'),'ramo@gmail.com','Namabia')
INTO customers VALUES (100356,'ramo',89638527411,to_date('13-04-2023','dd-mm-yyyy'),'ramo@gmail.com','SouthAfrica')
INTO customers VALUES (100356,'ramo',89638527413,to_date('13-04-2023','dd-mm-yyyy'),'ramo@gmail.com','England')
INTO customers VALUES (100356,'ramo',89638527415,to_date('13-04-2023','dd-mm-yyyy'),'ramo@gmail.com','Ontario')
INTO customers VALUES (100356,'ramo',89638527416,to_date('13-04-2023','dd-mm-yyyy'),'ramo@gmail.com','London')
INTO customers VALUES (100356,'ramo',89638527414,to_date('13-04-2023','dd-mm-yyyy'),'ramo@gmail.com','Mexica')
SELECT * FROM DUAL;

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