Override Identity Sequence when inserting a record

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
cResults
Posts: 17
Joined: Fri 09 Nov 2012 17:52

Override Identity Sequence when inserting a record

Post by cResults » Thu 15 Nov 2012 18:18

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
Posts: 17
Joined: Fri 09 Nov 2012 17:52

Re: Override Identity Sequence when inserting a record

Post by cResults » Thu 15 Nov 2012 18:34

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
Site Admin
Posts: 8245
Joined: Thu 14 Aug 2008 12:44

Re: Override Identity Sequence when inserting a record

Post by Shalex » Fri 16 Nov 2012 16:19

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

cResults
Posts: 17
Joined: Fri 09 Nov 2012 17:52

Re: Override Identity Sequence when inserting a record

Post by cResults » Fri 16 Nov 2012 18:27

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
Site Admin
Posts: 8245
Joined: Thu 14 Aug 2008 12:44

Re: Override Identity Sequence when inserting a record

Post by Shalex » Mon 19 Nov 2012 13:49

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
Posts: 17
Joined: Fri 09 Nov 2012 17:52

Re: Override Identity Sequence when inserting a record

Post by cResults » Mon 19 Nov 2012 22:52

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.

Shalex
Site Admin
Posts: 8245
Joined: Thu 14 Aug 2008 12:44

Re: Override Identity Sequence when inserting a record

Post by Shalex » Wed 21 Nov 2012 11:05

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
Posts: 17
Joined: Fri 09 Nov 2012 17:52

Re: Override Identity Sequence when inserting a record

Post by cResults » Mon 26 Nov 2012 23:07

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

Post Reply