Override Identity Sequence when inserting a record

Override Identity Sequence when inserting a record

Postby 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

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

Re: Override Identity Sequence when inserting a record

Postby Shalex » Fri 16 Nov 2012 16:19

You have found a solution for the approach you are using, haven't you?
Shalex
Devart Team
 
Posts: 7530
Joined: Thu 14 Aug 2008 12:44

Re: Override Identity Sequence when inserting a record

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

Re: Override Identity Sequence when inserting a record

Postby 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);
Shalex
Devart Team
 
Posts: 7530
Joined: Thu 14 Aug 2008 12:44

Re: Override Identity Sequence when inserting a record

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

Re: Override Identity Sequence when inserting a record

Postby 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);
Shalex
Devart Team
 
Posts: 7530
Joined: Thu 14 Aug 2008 12:44

Re: Override Identity Sequence when inserting a record

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


Return to dotConnect for Oracle