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. The second insert gives me an error: ORA-00933: SQL command not properly ended

What am I doing wrong?

Thanks!!

Answer 1

semi colon after the first insert?

Answer 2

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;

Answer 3

To me it seems you're missing a ; between the two statements: nsert 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.

Answer 4

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

Answer 5

Is the semicolon needed from OLE_DB ? It's not needed from most API's ?

Answer 6

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.

Answer 7

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

Thanks for your help anyway!!

Answer 8

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.