Page 1 of 1
Override Identity Sequence when inserting a record
Posted: Thu 15 Nov 2012 18:18
by cResults
We are using Entity Framework Code First which creates Oracle Sequences on all int primary keys where DatabaseGenerated(DatabaseGeneratedOption.Identity) (which is the default).
The sequences it creates start with 1 in both SQL Server and Oracle.
When creating a new record in Sql Server I can SET IDENTITY_INSERT ON to override the auto inserted primary key value with one I control (for example a negative number).
Is there a way to do this in PL/SQL?
Thank you for sharing you hard earned knowledge.
Re: Override Identity Sequence when inserting a record
Posted: Thu 15 Nov 2012 18:34
by cResults
I don't know if this is the Oracle way, but it seems to work.
Code: Select all
ALTER TRIGGER "TableName_INS_TRG" DISABLE;
INSERT INTO "TableName" ("TableNameID", "Name") VALUES(-1, ' ')
ALTER TRIGGER "TableName_INS_TRG" ENABLE;
Re: Override Identity Sequence when inserting a record
Posted: Fri 16 Nov 2012 16:19
by Shalex
You have found a solution for the approach you are using, haven't you?
Re: Override Identity Sequence when inserting a record
Posted: Fri 16 Nov 2012 18:27
by cResults
Can you tell me why this doesn't work? It throws "invalid character"
Code: Select all
var insertBaseEntityModel =
@"ALTER TRIGGER ""EntityModel_INS_TRG"" DISABLE;
INSERT INTO ""EntityModel"" (""EntityModelID"", ""Name"") VALUES (0, ' ');
ALTER TRIGGER ""EntityModel_INS_TRG"" ENABLE";
_DbContext.ExecuteSqlCommand(insertBaseEntityModel);
And this only works if the semi-colon is omitted.
Code: Select all
var insertBaseEntityModelDisable =
@"ALTER TRIGGER ""EntityModel_INS_TRG"" DISABLE";
var insertBaseEntityModel =
@"INSERT INTO ""EntityModel"" (""EntityModelID"", ""Name"") VALUES (0, ' ')";
var insertBaseEntityModelEnable =
@"ALTER TRIGGER ""EntityModel_INS_TRG"" ENABLE";
_DbContext.ExecuteSqlCommand(insertBaseEntityModelDisable);
_DbContext.ExecuteSqlCommand(insertBaseEntityModel);
_DbContext.ExecuteSqlCommand(insertBaseEntityModelEnable);
It appears that Oracle doens't like a C# ";"
I would much prefer to perform this action in a single transaction rather than 3 separate transactions.
Re: Override Identity Sequence when inserting a record
Posted: Mon 19 Nov 2012 13:49
by Shalex
OracleCommand is designed to execute only one SQL statement at a time. If you want to put several SQL statements in cmd.CommandText, please use the PL/SQL block:
Code: Select all
var insertBaseEntityModel =
@"BEGIN
ALTER TRIGGER ""EntityModel_INS_TRG"" DISABLE;
INSERT INTO ""EntityModel"" (""EntityModelID"", ""Name"") VALUES (0, ' ');
ALTER TRIGGER ""EntityModel_INS_TRG"" ENABLE;
END;";
_DbContext.ExecuteSqlCommand(insertBaseEntityModel);
Re: Override Identity Sequence when inserting a record
Posted: Mon 19 Nov 2012 22:52
by cResults
When calling
Shalex wrote:var insertBaseEntityModel =
@"BEGIN
ALTER TRIGGER ""EntityModel_INS_TRG"" DISABLE;
INSERT INTO ""EntityModel"" (""EntityModelID"", ""Name"") VALUES (0, ' ');
ALTER TRIGGER ""EntityModel_INS_TRG"" ENABLE;
END;";
_DbContext.ExecuteSqlCommand(insertBaseEntityModel);
The following error is received:
Code: Select all
ORA-06550: line 2, column 1:
PLS-00103: Encountered the symbol "ALTER" when expecting one of the following:
( begin case declare exit for goto if loop mod null pragma
raise return select update while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
continue close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge pipe purge
SaveChanges() is called on prior transactions right before this block is called.
Re: Override Identity Sequence when inserting a record
Posted: Wed 21 Nov 2012 11:05
by Shalex
Sorry for the mistake in my previous post (PL/SQL doesn't allow DDL statements). Try using anonymous PL/SQL block instead:
Code: Select all
var insertBaseEntityModel =
@"BEGIN
EXECUTE IMMEDIATE 'ALTER TRIGGER ""EntityModel_INS_TRG"" DISABLE';
EXECUTE IMMEDIATE 'INSERT INTO ""EntityModel"" (""EntityModelID"", ""Name"") VALUES (0, ' ')';
EXECUTE IMMEDIATE 'ALTER TRIGGER ""EntityModel_INS_TRG"" ENABLE';
END;";
_DbContext.ExecuteSqlCommand(insertBaseEntityModel);
Re: Override Identity Sequence when inserting a record
Posted: Mon 26 Nov 2012 23:07
by cResults
I needed to double up the single quotes that pass in the empty string. This is solved. Thank you for your assistance.
Shalex wrote:
Code: Select all
var insertBaseEntityModel =
@"BEGIN
EXECUTE IMMEDIATE 'ALTER TRIGGER ""EntityModel_INS_TRG"" DISABLE';
EXECUTE IMMEDIATE 'INSERT INTO ""EntityModel"" (""EntityModelID"", ""Name"") VALUES (0, '' '')';
EXECUTE IMMEDIATE 'ALTER TRIGGER ""EntityModel_INS_TRG"" ENABLE';
END;";
_DbContext.ExecuteSqlCommand(insertBaseEntityModel);