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.
In Oracle, you can only use one statement in each command.
;
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:
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;