Question
I basically generated a query inside a stringbuilder object which looks exactly like this:
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;
But when I run the following line: return con.Execute(SQL_Insert.ToString()); I get the following error:
Oracle.ManagedDataAccess.Client.OracleException: 'ORA-00933: SQL command not properly ended'
When I execute this query in Oracle SQL Developer, it'll insert all 5 rows and commit perfectly, no error messages or whatsoever.
Any suggestions on what I'm missing here?
I'm using Dapper as an ORM, in case that could be useful information.
Accepted Answer
You do not need the sub-query factoring clause and Oracle will not accept two commands (in this case, INSERT and COMMIT) in the same statement. SQL Developer will process them as two separate statements: an INSERT statement followed by a COMMIT statement.
So, remove the COMMIT statement:
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;
If your c# connection is not set to auto-commit then you may need to issue an explicit commit.
Also, if you are inserting DATE values then you should use a DATE/TIMESTAMP literal and you should review whether inserting numbers as strings is appropriate.
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow