Skip to Main Content
  • Questions
  • UPDATE Statement produces ORA-00933 when terminated with semicolon

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Emil.

Asked: September 01, 2022 - 9:05 am UTC

Last updated: September 06, 2022 - 1:15 am UTC

Version: 18.4

Viewed 100+ times

You Asked

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

and Connor said...

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.


Rating

  (1 rating)

Comments

No anonymous blocks that I know of

Emil Tchekov, September 05, 2022 - 9:49 am UTC

Dear Sir,

thank you very much for your response and your time!

I am using SQL parametrisation for atomic values - all of the items are plain types - there are no nested statement passed in there that I know of - just atomic values.

However I cannot know what the "black box" sanitizing algorythm is making out of my statement.

Is there a way to capture what exactly the routine is trying to execute after placing the values in the statement?

May be the problem is not the Oracle DB itself, but the .NET driver and value sanitation routine - which is producing improperly ended statement?

Best regards

Emil Tchekov


Connor McDonald
September 06, 2022 - 1:15 am UTC

Try adding a session level trace, ie

dbms_monitor.session_trace_enable

if you are using a connection pool,. you might need to enable it for all connections in the pool to ensure that you capture the statement.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library