ORA-01400 when trying to insert into schema.table.ID

I have a table that stores rejected contract proposals.

CREATE TABLE "STATUS_CONTRATO" ( "STC_ID" NUMBER NOT NULL, "CTB_CONTRATO" NUMBER NOT NULL, "STC_DATA" DATE NOT NULL, "STC_OBSERVACAO" VARCHAR2(200) NOT NULL, CONSTRAINT "STATUS_CONTRATO_PK" PRIMARY KEY ( "STC_ID") ENABLE VALIDATE, CONSTRAINT "FK_CONTRATO" FOREIGN KEY ( "CTB_CONTRATO") REFERENCES "CONTRATO" ( CTB_CONTRATO) ON DELETE SET NULL ENABLE VALIDATE) ; 

(Script generated by Visual Studio 2010)

There is a simple trigger in this table where STC_ID :

 TRIGGER "STATUS_CONTRATO_TRIGGER1" BEFORE INSERT ON "STATUS_CONTRATO" FOR EACH ROW when (new.STC_ID = 0) DECLARE BEGIN SELECT SEQ_STATUS_ID.NEXTVAL INTO :NEW.STC_ID FROM DUAL; END; 

SEQ_STATUS_ID is a simple sequence.

Here is my problem:

I can successfully complete this insertion in the VS2010 request window:

 insert into myschema.STATUS_CONTRATO s( s.STC_ID, s.CTB_CONTRATO, s.STC_DATA, s.STC_OBSERVACAO )values( 0, 10, SYSDATE, 'Inserting by hand works' ); 

But when I try to insert using EF, I get this exception:

 System.Data.UpdateException: An error occurred while updating the entries. See the inner exception for details. ---> Oracle.DataAccess.Client.OracleException: ORA-01400: cannot insert NULL into ("MYSCHEMA"."STATUS_CONTRATO"."STC_ID") ORA-06512: at line 4 

I use this code to insert

 STATUS_CONTRATO statusContrato = new STATUS_CONTRATO() { STC_ID = 0, CTB_CONTRATO = codContrato, STC_DATA = DateTime.Today, STC_OBSERVACAO = observacao }; ent.STATUS_CONTRATO.AddObject(statusContrato); ent.SaveChanges(); 

I am using VS2010, Oracle 11g (CentOS Server), ODP.NET client 11.2.0.3.0 Production, .NET Framework 4.0, EF 4.

+4
source share
3 answers

Check it out: http://www.oracle.com/technetwork/issue-archive/2011/11-sep/o51odt-453447.html

In particular, the section "Triggers and sequences"

From the Tools menu, select Run SQL Plus Script. Go to where you extracted the code and scripts, select triggers.sql script, select an HR connection from the list and click Run. The INSERTEMPLOYEES trigger created by the script generates a new sequence for EMPLOYEE_ID when NULL is passed for this value ........

+3
source

Your code works, except for a problem with

 ent.UNV_STATUS_CONTRATO.AddObject(statusContrato); 

Is UNV_STATUS_CONTRATO another table? Why is it not

 ent.STATUS_CONTRATO.AddObject(statusContrato); 

This should work fine.

However, you might find it preferable to get the sequence value from your code and apply it to the identifier column in memory before saving the changes. So something like this:

  public static int GetSequenceNextVal() { using (YourEntities entities = new YourEntities ()) { var sql = "select myschema.SEQ_STATUS_ID.NEXTVAL from dual"; var qry = entities.ExecuteStoreQuery<decimal>(sql); return (int)qry.First(); } } 

Then you call this method before SaveChanges() .

Personally, I prefer to handle it in such a way that my in-memory object would then have the correct ID, instead of just having 0 or requiring it back, etc.

In addition, the method described here in the Triggers and Sequences section can presumably connect a PK object to a sequence.

+1
source

You need to indicate in your EF mapping that the database is not generating a key for you, and EF should use the key you provided ...

see my post in the following thread: fooobar.com/questions/1428795 / ...

0
source

All Articles