ORA-00933: SQL command not properly ended

Question

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

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)

The first insert succeeds but the second one gives an error:

ORA-00933: SQL command not properly ended

What am I doing wrong?

Understanding

Are you trying to do both inserts at once? – Montalvo

Answer 1

To me it seems you're missing a ; between the two statements:
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)
;
Try adding the ; and let us know.

Sandell

Answer 2

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:

Dim db As Database = DatabaseFactory.CreateDatabase("db")
Dim cmd As System.Data.Common.DbCommand
Dim sql As String = ""

sql = "DELETE FROM iphone_applications WHERE appid = 1; DELETE FROM iphone_applications WHERE appid = 2; "

cmd = db.GetSqlStringCommand(sql)
db.ExecuteNonQuery(cmd)

The code above will give you the same Oracle error: ora-00911: invalid character.

The solution to this problem is to wrap your 2 Oracle SQL statements with a BEGIN and END; syntax, for example:

sql = "BEGIN DELETE FROM iphone_applications WHERE appid = 1; DELETE FROM iphone_applications WHERE appid = 2; END;"

Courtesy: http://www.lazyasscoder.com/Article.aspx?id=89&title=ora-00911%3A+invalid+character+when+executing+multiple+Oracle+SQL+statements

Oswaldooswalt

when you are entering code in a question/answer, it is more readable if you format it as code. The "{}" button does this for you, or you can just indent the code manually. you can also use back ticks to mark code within a sentence. – Valero

The "lazyasscoder" link above sends me to a website that flashes using Chinese characters. Please be careful. Am, not sure whether I have got accidentally infected – Teacart

Answer 3

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.

Pellmell

Answer 4

semi colon after the first insert?

Calan

Adding a semi-colon to the end of the statement gives me this error: ORA-00911: invalid character – Understanding

Answer 5

Oracle SQL uses a semi-colon ; as its end of statement marker.

you will need to add the ; after bother insert statments.

NB: that also assumes ADODB will allow 2 inserts in a single call.

the alternative might be to wrap both calls in a block,

BEGIN
      insert (...) into (...);
      insert (...) into (...);
END;

Precedential

Answer 6

In my loop I was not re-initializing my StringBuilder ...thus the multiple insert statement I posted.

Thanks for your help anyway!!

Understanding

Answer 7

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 ...

Feininger

Answer 8

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.

Softboiled

Answer 9

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.

Organizer

Answer 10

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.

Inflation