<p>I'm using OLEDB provider for ADO.Net connecting to an Oracle database.  In my loop, I am doing an insert:</p>

<pre><code>insert into ps_tl_compleave_tbl values('2626899', 0, TO_DATE('01/01/2002', 'MM/DD/YYYY'), 'LTKN', 'LTKN', '52', TO_DATE('01/01/2002', 'MM/DD/YYYY'), 16.000000, 24.000)insert into ps_tl_compleave_tbl values('4327142', 0, TO_DATE('03/23/2002', 'MM/DD/YYYY'), 'LTKN', 'LTKN', '51', TO_DATE('03/23/2002', 'MM/DD/YYYY'), 0.000000, 0.000)
</code></pre>

<p>The first insert succeeds but the second one gives an error:</p>

<pre><code>ORA-00933: SQL command not properly ended
</code></pre>

<p>What am I doing wrong?</p>

[{'answer_id': 72165, 'author': 'metadave', 'author_id': 7237, 'author_profile': 'https://Stackoverflow.com/users/7237', 'pm_score': 2, 'selected': False, 'text': '<p>semi colon after the first insert?</p>\n'}, {'answer_id': 72170, 'author': 'ShoeLace', 'author_id': 3825, 'author_profile': 'https://Stackoverflow.com/users/3825', 'pm_score': 2, 'selected': False, 'text': '<p>Oracle SQL uses a semi-colon ;  as its end of statement marker.</p>\n\n<p>you will need to add the ; after bother insert statments.</p>\n\n<p>NB: that also assumes ADODB will allow 2 inserts in a single call.</p>\n\n<p>the alternative might be to wrap both calls in a block,</p>\n\n<pre><code>BEGIN\n      insert (...) into (...);\n      insert (...) into (...);\nEND;\n</code></pre>\n'}, {'answer_id': 72179, 'author': 'massimogentilini', 'author_id': 11673, 'author_profile': 'https://Stackoverflow.com/users/11673', 'pm_score': 3, 'selected': True, 'text': "<p>To me it seems you're missing a <code>;</code> between the two statements:<br>\n<code>insert into ps_tl_compleave_tbl values('2626899', 0, TO_DATE('01/01/2002', 'MM/DD/YYYY'), 'LTKN', 'LTKN', '52', TO_DATE('01/01/2002', 'MM/DD/YYYY'), 16.000000, 24.000)</code><br>\n<strong><code>;</code></strong><br>\n<code>insert into ps_tl_compleave_tbl values('4327142', 0, TO_DATE('03/23/2002', 'MM/DD/YYYY'), 'LTKN', 'LTKN', '51', TO_DATE('03/23/2002', 'MM/DD/YYYY'), 0.000000, 0.000)</code><br>\n<strong><code>;</code></strong><br>\nTry adding the <code>;</code> and let us know.</p>\n"}, {'answer_id': 72228, 'author': 'stevechol', 'author_id': 2981, 'author_profile': 'https://Stackoverflow.com/users/2981', 'pm_score': 0, 'selected': False, 'text': "<p>It's a long shot but in the first insert the sql date format is valid for both uk/us, the second insert is invalid if the Oracle DB is setup for UK date format, I realise you have used the TO_DATE function but I don't see anything else ...</p>\n"}, {'answer_id': 72493, 'author': 'Mike Dimmick', 'author_id': 6970, 'author_profile': 'https://Stackoverflow.com/users/6970', 'pm_score': 0, 'selected': False, 'text': "<p>The ADO.NET OLE DB provider is for generic data access where you don't have a specific provider for your database. Use OracleConnection et al in preference to OleDbConnection for an Oracle database connection.</p>\n"}, {'answer_id': 72967, 'author': 'Steve Horn', 'author_id': 10589, 'author_profile': 'https://Stackoverflow.com/users/10589', 'pm_score': 1, 'selected': False, 'text': '<p>In my loop I was not re-initializing my StringBuilder ...thus the multiple insert statement I posted.</p>\n\n<p>Thanks for your help anyway!!</p>\n'}, {'answer_id': 78572, 'author': 'Jon Ericson', 'author_id': 1438, 'author_profile': 'https://Stackoverflow.com/users/1438', 'pm_score': 0, 'selected': False, 'text': '<p>In addition to the semicolon problem, I strongly recommend you look into bind variables.  Failing to use them can cause database performance problems down the road.  The code also tends to be cleaner.</p>\n'}, {'answer_id': 6056951, 'author': 'taranjeet', 'author_id': 760830, 'author_profile': 'https://Stackoverflow.com/users/760830', 'pm_score': 4, 'selected': False, 'text': '<p>In .net, when we try to execute a single Oracle SQL statement with a semicolon at the end. The result will be an oracle error: ora-00911: invalid character.  OK, you figure that one SQL statement doesn\'t need the semicolon, but what about executing 2 SQL statement in one string for example:</p>\n\n<pre><code>Dim db As Database = DatabaseFactory.CreateDatabase("db")\nDim cmd As System.Data.Common.DbCommand\nDim sql As String = ""\n\nsql = "DELETE FROM iphone_applications WHERE appid = 1; DELETE FROM iphone_applications WHERE appid = 2; "\n\ncmd = db.GetSqlStringCommand(sql)\ndb.ExecuteNonQuery(cmd)\n</code></pre>\n\n<p>The code above will give you the same Oracle error: ora-00911: invalid character.</p>\n\n<p>The solution to this problem is to wrap your 2 Oracle SQL statements with a <code>BEGIN</code> and <code>END;</code> syntax, for example:</p>\n\n<pre><code>sql = "BEGIN DELETE FROM iphone_applications WHERE appid = 1; DELETE FROM iphone_applications WHERE appid = 2; END;"\n</code></pre>\n\n<p>Courtesy: <a href="http://www.lazyasscoder.com/Article.aspx?id=89&amp;title=ora-00911%3A+invalid+character+when+executing+multiple+Oracle+SQL+statements" rel="noreferrer">http://www.lazyasscoder.com/Article.aspx?id=89&amp;title=ora-00911%3A+invalid+character+when+executing+multiple+Oracle+SQL+statements</a></p>\n'}, {'answer_id': 17193880, 'author': 'CrackMonkeys4Hire', 'author_id': 2501607, 'author_profile': 'https://Stackoverflow.com/users/2501607', 'pm_score': 3, 'selected': False, 'text': "<p>In Oracle the semi-colon ';' is only used in sqlplus. When you are using ODBC/JDBC, OLEDB, etc you don't put a semi-colon at the end of your statement. In the above case you are actually executing 2 different statements so the best way to handle the problem is use 2 statements instead of trying to combine into a single statement since you can't use the semi-colon.</p>\n"}, {'answer_id': 48570003, 'author': 'Kevin Weinhold', 'author_id': 1445190, 'author_profile': 'https://Stackoverflow.com/users/1445190', 'pm_score': 0, 'selected': False, 'text': '<p>The issue may be that you have a parameter variable that is null being inserted into the query. That was what my problem was. Once I gave the parameter a default value of empty string, it worked.</p>\n'}]
['https://Stackoverflow.com/questions/72151', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/10589/']