Override Identity Sequence when inserting a record

[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.

[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;

[Shalex]

You have found a solution for the approach you are using, haven't you?

[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.

[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);

[cResults]

When calling

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.

[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);

[cResults]

I needed to double up the single quotes that pass in the empty string. This is solved. Thank you for your assistance.

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);