c# - 尝试插入 schema.table.ID 时出现 ORA-01400

Question

我有一张表存储被拒绝的契约(Contract)提案。

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

(由 Visual Studio 2010 生成的脚本)

这个表有一个简单的触发器，其中设置了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 是一个简单的序列。

这是我的问题:

我可以在 VS2010 查询窗口中成功执行此插入操作:

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

但是，当我尝试使用 EF 插入时，我遇到了这个异常:

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

我正在使用这段代码来插入

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

我正在使用 VS2010、Oracle 11g(CentOS 服务器)、ODP.NET 客户端 11.2.0.3.0 生产版、.NET Framework 4.0、EF 4。

Answer 1

检查这个:http://www.oracle.com/technetwork/issue-archive/2011/11-sep/o51odt-453447.html

特别是“触发器和序列”部分

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