Hi,
I have simple parametrised UPDATE statement (.NET core application, Oracle .NET Drivers)
UPDATE T_SLMGM_SLPLAN SET BEGINN = :Beginn, ENDE = :Ende, UPDATE_TIMESTAMP = :Update_Timestamp, OPTLOCK = :Optlock, CREATE_TIMESTAMP = :Create_Timestamp, CREATE_USER = :Create_User, STATUS = :Status, UPDATE_USER = :Update_User, LADESTELLE = :Ladestelle WHERE ID = :Id
However if the statement is terminated with Semicolon - it will reproducibly fail with ORA-00933 "not properly terminated"
So problem is located and easily fixed, but still want to know why is that so.
Isnt Semicolon at the end of statement SQL standart?
SELECT, INSERT and DELETE statements does not seem to be affected and can be terminated with semicolon.
Thank you very much for the clarification!
Best regards
Emil
Are you sure you are not using anonymous blocks here? There's a few "meanings" to semi-colon
- in native SQL => means nothing (invalid)
- in PL/SQL => end of statement
- in SQL Plus => please run the SQL now (set sqlterminator).
Here's examples using dynamic SQL in PLSQL to mimic what you *might* be seeing
SQL> create table t as select * from scott.emp;
Table created.
SQL>
SQL>
--
-- semi-colon is NOT SQL, but an instruction to SQL Plus
--
SQL> update t set sal = sal + 1;
14 rows updated.
--
-- hence use of semi-colon in pure SQL is invalid
--
SQL>
SQL> begin
2 execute immediate 'update t set sal = sal + 1;';
3 end;
4 /
begin
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
ORA-06512: at line 2
SQL>
SQL>
SQL> begin
2 execute immediate 'update t set sal = sal + 1';
3 end;
4 /
PL/SQL procedure successfully completed.
--
-- but semi-colon in PLSQL is valid because it marks the END of the statement
--
SQL> begin
2 update t set sal = sal + 1;
3 end;
4 /
PL/SQL procedure successfully completed.