无法在Visual Studio中运行SQL查询，但在Oracle SQL Developer中工作

Question

我基本上在一个字符串生成器对象中生成了一个查询，看起来像这样：

SQL_Insert

INSERT INTO S1589_XML_Ptdes(ID, VALID_FROM_DATE, VALID_TO_DATE, SYMBOLIC_NAME, SHORT_NAME_FRENCH, SHORT_NAME_DUTCH, LONG_NAME_FRENCH, LONG_NAME_DUTCH, PTCAR_ID, DISTANCE_FROM_PTCAR, TYPE, LAST_UPDATE_DATE)
WITH S1589_XML_Ptdes AS (
select '142','1996-06-02','2006-05-31','111001','Quais','Perrons','Quais Gand St Pierre','Perrons Gent St Pieters','455','0','2','2010-06-04T06:21:35' from dual 
union all select '142','2006-06-01','2010-02-10','111001','Quais','Perrons','Quais Gand St Pierre','Perrons Gent St Pieters','455','-38','2','2010-06-04T06:21:35' from dual 
union all select '142','2010-02-11','2011-04-20','111001','Gand St. P','Gent St.P','Gand-Saint-Pierre','Gent-Sint-Pieters','455','-38','2','2011-04-26T09:08:00' from dual 
union all select '142','2011-04-21','2014-01-19','111001','Gent St.P','Gent St.P','Gent-Sint-Pieters','Gent-Sint-Pieters','455','-38','2','2014-01-20T14:06:39' from dual 
union all select '142','2014-01-20','2015-12-17','111001','Gent St.P','Gent St.P','Gent-Sint-Pieters','Gent-Sint-Pieters','455','0','2','2015-12-16T18:10:31' from dual 
) SELECT * FROM S1589_XML_Ptdes; COMMIT;

但是当我运行以下行时：return con.Execute(SQL_Insert.ToString());出现以下错误：

Oracle.ManagedDataAccess.Client.OracleException： 'ORA-00933： SQL 命令未正确结束'

当我在Oracle SQL开发人员中执行此查询时，它将插入所有5行并完美提交，没有错误消息或任何内容。

关于我在这里缺少什么的任何建议？

我正在使用Dapper作为ORM，以防这可能是有用的信息。

Answer 1

您不需要子查询分解子句，Oracle 将不接受同一语句中的两个命令(在本例中为 INSERT 和 COMMIT(。SQL Developer 将将它们作为两个单独的语句进行处理：一个INSERT语句，后跟一个COMMIT语句。

因此，删除COMMIT语句：

INSERT INTO S1589_XML_Ptdes(ID, VALID_FROM_DATE, VALID_TO_DATE, SYMBOLIC_NAME, SHORT_NAME_FRENCH, SHORT_NAME_DUTCH, LONG_NAME_FRENCH, LONG_NAME_DUTCH, PTCAR_ID, DISTANCE_FROM_PTCAR, TYPE, LAST_UPDATE_DATE)
  select '142', DATE '1996-06-02', DATE '2006-05-31','111001','Quais','Perrons','Quais Gand St Pierre','Perrons Gent St Pieters','455','0','2', TIMESTAMP '2010-06-04 06:21:35' from dual union all
  select '142', DATE '2006-06-01', DATE '2010-02-10','111001','Quais','Perrons','Quais Gand St Pierre','Perrons Gent St Pieters','455','-38','2', TIMESTAMP '2010-06-04 06:21:35' from dual union all
  select '142', DATE '2010-02-11', DATE '2011-04-20','111001','Gand St. P','Gent St.P','Gand-Saint-Pierre','Gent-Sint-Pieters','455','-38','2', TIMESTAMP '2011-04-26 09:08:00' from dual union all
  select '142', DATE '2011-04-21', DATE '2014-01-19','111001','Gent St.P','Gent St.P','Gent-Sint-Pieters','Gent-Sint-Pieters','455','-38','2', TIMESTAMP '2014-01-20 14:06:39' from dual union all
  select '142', DATE '2014-01-20', DATE '2015-12-17','111001','Gent St.P','Gent St.P','Gent-Sint-Pieters','Gent-Sint-Pieters','455','0','2', TIMESTAMP '2015-12-16 18:10:31' from dual;

如果 c# 连接未设置为自动提交，则可能需要发出显式提交。

此外，如果要插入DATE值，则应使用DATE/TIMESTAMP文本，并且应检查插入数字作为字符串是否合适。