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

Question

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)

This table has a simple Trigger, where the value of STC_ID is set:

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's my problem:

I can successfuly execute this insert in the VS2010 query 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'm getting 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'm using 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'm using VS2010, Oracle 11g (CentOS Server), ODP.NET client 11.2.0.3.0 Production, .NET Framework 4.0, EF 4.

Try using System.Data.OracleClient and check if the problem persists — Nathan, Aug 15 '12 at 15:49
I've changed from `System.Data.OracleClient` because it wasn't correctly binding the parameters in another part of the system — Timoteo Brasil, Aug 15 '12 at 16:09

score 4 · Accepted Answer · answered Aug 15 '12 at 16:25

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

Particularly, section "Triggers and Sequences"

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

worked by changing the trigger to look like the one provided in the article's zipfile and commenting the `STC_ID = 0` statement — Timoteo Brasil, Aug 15 '12 at 21:06

hmqcnoesy · Answer 2 · 2012-08-15T16:35:55.933

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

That should work just fine.

However, you might find it preferable to get the sequence value from your code and apply it to your ID column in memory before saving 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 that method before SaveChanges().

Personally, I prefer to handle it this way so that my in-memory entity then also has the correct ID, instead of just having a 0, or having to query it right back out, etc.

Also, the method described here under Triggers and Sequences can supposedly wire up an entity's PK to a sequence.

`UNV_` is just the prefix we're using for the tables. I omitted it in the other parts of the question and forgot omitting here. — Timoteo Brasil, Aug 15 '12 at 19:23
`StoreGeneratedPattern="Identity"` Has already been set in the .edmx file. — Timoteo Brasil, Aug 15 '12 at 19:40

score 0 · Answer 3 · edited May 23 '17 at 11:56

0

You need to specify in your EF Mapping that the DB does not generate the key for you and EF should use the Key you provided...

see my post in the following thread: https://stackoverflow.com/a/18635325/1712367

edited May 23 '17 at 11:56

Community

1
1

answered Sep 05 '13 at 11:50

pastrami01

341
4
9

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

3 Answers3