Home ORA-00933: SQL command not properly ended in C# with Entity Framework (INSERT ALL with commit)

Questions

ORA-00933: SQL command not properly ended in C# with Entity Framework (INSERT ALL with commit)

September 4, 2024

I try to read a SQL file during the Entity Framework update-database command:

public override void Up()
{
    this.SqlResource("Migrations.SqlMigrations.ImportData.sql");
}

Update-Database -SourceMigration 0 -StartUpProjectName Test

The reading works fine, but then I get the above error. I know there is some tricky behavior with the ; semicolon in C# and it doesn’t work like in the Oracle SQL Developer.

Here is the code from my SQL file:

INSERT ALL 
INTO PLZ (PLZ_PK, PLZ, LOCATION, CREATION_DATE, CREATED_BY) 
VALUES ('1', '111111', 'Testcity1', to_timestamp('15.12.09 09:15:00,000000000'), 'Init Dateimport')
INTO PLZ (PLZ_PK, PLZ, LOCATION, CREATION_DATE, CREATED_BY) 
VALUES ('2', '222222', 'Testcity2', to_timestamp('15.12.09 09:15:00,000000000'), 'Init Dateimport')
...
..
.
SELECT * FROM dual
COMMIT

INSERT ALL 
INTO PLZ (PLZ_PK, PLZ, LOCATION, CREATION_DATE, CREATED_BY) 
VALUES ('3', '333333', 'Testcity3', to_timestamp('15.12.09 09:15:00,000000000'), 'Init Dateimport')
...
..
.
SELECT * FROM dual

The points (…) are not in the file, I just used them to represent that between the commit you have 500 rows. The splitting of the INSERT ALL statement with COMMIT works fine in Oracle SQL Developer and I don’t get the error

PL/SQL: ORA-00913: Too many values

which usually occurs.

>Solution :

In Oracle, you can only use one statement in each command.

If your code is reading the SQL file and passing it in its entirety to Oracle in a single command then you will get a syntax error.
If your code is being passed to a pre-processor and that pre-processor will parse it and send individual statements to the database then you need to consult the documentation for that pre-processor – but the pre-processor will probably be looking for the statement terminators which are either ; or /.

Looking at this answer it appears that SqlResource just reads the file and passes the content to SqlOperation and that "Represents a provider specific SQL statement to be executed directly against the target database" so it is not pre-processing the statement in any way and is just passing it directly to the database. Therefore, Oracle’s restrictions on a single statement-per-command (probably) apply.

You would need to either:

split it up into individual DML statements (which would not want the trailing semi-colons); or
wrap it in a PL/SQL block (which would want the trailing semi-colons for the SQL statement terminators but would not want the trailing slash for the PL/SQL statement terminator):

BEGIN
  INSERT ALL 
    INTO PLZ (PLZ_PK, PLZ, LOCATION, CREATION_DATE, CREATED_BY) 
    VALUES ('1', '111111', 'Testcity1', to_timestamp('15.12.09 09:15:00,000000000'), 'Init Dateimport')
    INTO PLZ (PLZ_PK, PLZ, LOCATION, CREATION_DATE, CREATED_BY) 
    VALUES ('2', '222222', 'Testcity2', to_timestamp('15.12.09 09:15:00,000000000'), 'Init Dateimport')
    -- ...
    -- ..
    -- .
    SELECT * FROM dual;

  INSERT ALL 
    INTO PLZ (PLZ_PK, PLZ, LOCATION, CREATION_DATE, CREATED_BY) 
    VALUES ('3', '333333', 'Testcity3', to_timestamp('15.12.09 09:15:00,000000000'), 'Init Dateimport')
    -- ...
    -- ..
    -- .
    SELECT * FROM dual;

  COMMIT;
END;