Entity Framework的Add()到ORACLE数据库引发了虚拟列上不允许的INSERT操作。

huangapple go评论65阅读模式
英文:

Entity Framework Add() to ORACLE DB raises INSERT operation disallowed on virtual columns

问题

以下是翻译好的部分:

我有一个Oracle数据库,其中有多个表(其中三个表具有始终生成的身份ID列),需要连接到Windows表单。

BREAKDOWN 表

CREATE TABLE "BREAKDOWN" 
   ("EQUIPMENTID" NCHAR(8) NOT NULL ENABLE, 
	"FROMTIME" TIMESTAMP (4), 
	"TOTIME" TIMESTAMP (4), 
	"WORKORDER" NUMBER(10,0) NOT NULL ENABLE, 
	"BDNREASON" NCHAR(10), 
	"BDNTIME" INTERVAL DAY (2) TO SECOND (6) GENERATED ALWAYS AS (("TOTIME"-"FROMTIME") DAY(9) TO SECOND(4)) VIRTUAL , 
	"BREAKDOWNID" NUMBER(10,0) GENERATED ALWAYS AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE  NOT NULL ENABLE, 
	 CONSTRAINT "BREAKDOWN_PK" PRIMARY KEY ("BREAKDOWNID") )

BREAKDOWN 生成的类

public partial class BREAKDOWN
{
    public string EQUIPMENTID { get; set; }
    public Nullable<System.DateTime> FROMTIME { get; set; }
    public Nullable<System.DateTime> TOTIME { get; set; }
    public int WORKORDER { get; set; }
    public string BDNREASON { get; set; }
    public int BREAKDOWNID { get; set; }
    public Nullable<decimal> BDNTIME { get; set; }

    public virtual EQUIPMENT EQUIPMENT { get; set; }
    public virtual WORKORDER WORKORDER1 { get; set; }
}

WORKORDER 表

CREATE TABLE "WORKORDER" 

   (
	"WORKORDERTYPEID" NCHAR(3) NOT NULL ENABLE, 
	"WORKORDEREQUIPMENTID" NCHAR(8) NOT NULL ENABLE, 
	"WORKORDERREGISTERATIONDATE" TIMESTAMP (3) NOT NULL ENABLE, 
	"WORKORDERID" NUMBER(10,0) GENERATED ALWAYS AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 NOCACHE  NOORDER  NOCYCLE  NOT NULL ENABLE, 
	 CONSTRAINT "WORKORDER_PK" PRIMARY KEY ("WORKORDERID")
)

WORKORDER 生成的类

public partial class WORKORDER
{
    public WORKORDER()
    {
        this.BREAKDOWNs = new HashSet<BREAKDOWN>();
    }
    public string WORKORDERTYPEID { get; set; }
    public string WORKORDEREQUIPMENTID { get; set; }
    public System.DateTime WORKORDERREGISTERATIONDATE { get; set; }
    public int WORKORDERID { get; set; }
}

身份ID在使用Oracle SQL Developer进行插入操作时正常工作,但如果尝试使用Windows表单视图进行插入(使用Add()函数),则会引发上述错误。这仅发生在BREAKDOWN表中,而WORKORDER表正常运行。我已经确保以下内容:

  • 所有列都具有所需的ISEQ身份。
  • 所有列的数据类型在DBTables和Entities中都是相同的。
  • Oracle正确计算了身份属性(使用Oracle SQL Developer手动插入)。
英文:

I have Oracle DB that has multiple Tables (Three of them have ID Column with ALWAYS GENERATED IDENTITY) that is required to be connected to Windows Form.

> BREAKDOWN Table

CREATE TABLE &quot;BREAKDOWN&quot; 
   (&quot;EQUIPMENTID&quot; NCHAR(8) NOT NULL ENABLE, 
	&quot;FROMTIME&quot; TIMESTAMP (4), 
	&quot;TOTIME&quot; TIMESTAMP (4), 
	&quot;WORKORDER&quot; NUMBER(10,0) NOT NULL ENABLE, 
	&quot;BDNREASON&quot; NCHAR(10), 
	&quot;BDNTIME&quot; INTERVAL DAY (2) TO SECOND (6) GENERATED ALWAYS AS ((&quot;TOTIME&quot;-&quot;FROMTIME&quot;)DAY(9) TO SECOND(4)) VIRTUAL , 
	&quot;BREAKDOWNID&quot; NUMBER(10,0) GENERATED ALWAYS AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE  NOT NULL ENABLE, 
	 CONSTRAINT &quot;BREAKDOWN_PK&quot; PRIMARY KEY (&quot;BREAKDOWNID&quot;) )

> BREAKDOWN Generated Class

public partial class BREAKDOWN
    {
        public string EQUIPMENTID { get; set; }
        public Nullable&lt;System.DateTime&gt; FROMTIME { get; set; }
        public Nullable&lt;System.DateTime&gt; TOTIME { get; set; }
        public int WORKORDER { get; set; }
        public string BDNREASON { get; set; }
        public int BREAKDOWNID { get; set; }
        public Nullable&lt;decimal&gt; BDNTIME { get; set; }
    
        public virtual EQUIPMENT EQUIPMENT { get; set; }
        public virtual WORKORDER WORKORDER1 { get; set; }
    }

> WORKORDER Table

CREATE TABLE &quot;WORKORDER&quot; 

       (
    	&quot;WORKORDERTYPEID&quot; NCHAR(3) NOT NULL ENABLE, 
    	&quot;WORKORDEREQUIPMENTID&quot; NCHAR(8) NOT NULL ENABLE, 
    	&quot;WORKORDERREGISTERATIONDATE&quot; TIMESTAMP (3) NOT NULL ENABLE, 
    	&quot;WORKORDERID&quot; NUMBER(10,0) GENERATED ALWAYS AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 NOCACHE  NOORDER  NOCYCLE  NOT NULL ENABLE, 
    	 CONSTRAINT &quot;WORKORDER_PK&quot; PRIMARY KEY (&quot;WORKORDERID&quot;)
    )

> WORKORDER Generated Class

public partial class WORKORDER
    {
        public WORKORDER()
        {
            this.BREAKDOWNs = new HashSet&lt;BREAKDOWN&gt;();
        }
        public string WORKORDERTYPEID { get; set; }
        public string WORKORDEREQUIPMENTID { get; set; }
        public System.DateTime WORKORDERREGISTERATIONDATE { get; set; }
        public int WORKORDERID { get; set; }
    }

IDENTITY is working properly for the three tables if I tried to INSERT using ORACLE SQL Developer

whileas if I tried to insert using the Windows form View using (Add() Function) it raises the mentioned Error:

> System.Data.Entity.Infrastructure.DbUpdateException: 'An error
> occurred while updating the entries. See the inner exception for
> details.' UpdateException: An error occurred while updating the
> entries. See the inner exception for details. OracleException:
> ORA-54013: INSERT operation disallowed on virtual columns ORA-06512:
> at line 4

This occurs for only BREAKDOWN

BREAKDOWN newBDN = new BREAKDOWN();
newBDN.WORKORDER = workOrder.WORKORDERID;
newBDN.EQUIPMENTID = workOrder.WORKORDEREQUIPMENTID;
newBDN.FROMTIME = bdnFromDateTimePicker.Value;
home.dbContext.BREAKDOWNs.Add(newBDN);
home.dbContext.SaveChanges();

while WORKORDER table is operating properly!!

WORKORDER newWorkOrder= new WORKORDER ();
newWorkOrder.EQUIPMENT = (EQUIPMENT) equipmentBindingSource.DataSource;
newWorkOrder.WORKORDERREGISTERATIONDATE = regsiterationDatePicker.Value;
newWorkOrder.WORKORDERTYPE = (WORKORDERTYPE)workTypeTextBox.SelectedItem;
home.dbContext.WORKORDERs.Add(newWorkOrder);
home.dbContext.SaveChanges();

I have assured that following:

  • Columns are all IDENTITY with the required ISEQ.
  • Data types for all the Columns are identical in both DBTables and Entites.
  • ORACLE is calculating IDENTITY Property properly (Manual Inserting using ORACLE SQL Developer)

答案1

得分: 1

我会说这不是标识列,而是另一个虚拟列。例如,像这样的东西:请看第7行,哪里有一个虚拟列?

SQL> 创建表test_so
2 (
3 id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
4 名称VARCHAR2(10),
5 薪水NUMBER,
6 佣金NUMBER,
7 总数NUMBER GENERATED ALWAYS AS(工资+佣金)虚拟
8 );

表已创建。

如果我尝试插入值,Oracle会引发错误(与您收到的相同):

SQL>插入test_so(名称,工资,佣金,总额)
2值('小',100,20,120);
插入test_so(名称,工资,佣金,总额)
*

在第1行发生错误:
ORA-54013:禁止对虚拟列进行插入操作

如果我省略总数

SQL>插入test_so(名称,工资,佣金)
2值('小',100,20);

1行已创建。

SQL>从test_so中选择*;

ID NAME SALARY COMMISSION TOTAL


1 小100 20 120

1行已选择。

SQL>;


因此,请检查这些表中是否有任何虚拟列。如何?像这样:

SQL>从user_tab_columns中选择column_name,data_default
2 WHERE table_name ='TEST_SO';

列名数据默认值


ID "SCOTT"。"ISEQ$$_258989"。nextval
名字
薪水
佣金
总计 "工资" + "佣金"

选择了5行。

SQL>;

英文:

I'd say that it is not the identity column, but another - virtual column. For example, something like this: see line #7, which is a virtual column?

SQL&gt; CREATE TABLE test_so
  2  (
  3     id           NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  4     name         VARCHAR2 (10),
  5     salary       NUMBER,
  6     commission   NUMBER,
  7     total        NUMBER GENERATED ALWAYS AS (salary + commission) VIRTUAL
  8  );

Table created.

If I try to insert value into it, Oracle raises error (the same you got):

SQL&gt; insert into test_so (name, salary, commission, total)
  2    values (&#39;Little&#39;, 100, 20, 120);
insert into test_so (name, salary, commission, total)
                                               *
ERROR at line 1:
ORA-54013: INSERT operation disallowed on virtual columns

If I omit total:

SQL&gt; insert into test_so (name, salary, commission)
  2    values (&#39;Little&#39;, 100, 20);

1 row created.

SQL&gt; select * from test_so;

        ID NAME           SALARY COMMISSION      TOTAL
---------- ---------- ---------- ---------- ----------
         1 Little            100         20        120

1 row selected.

SQL&gt;

Therefore, check whether there's any virtual column in those tables. How? Like this:

SQL&gt; SELECT column_name, data_default
  2    FROM user_tab_columns
  3   WHERE table_name = &#39;TEST_SO&#39;;

COLUMN_NAME     DATA_DEFAULT
--------------- ----------------------------------------
ID              &quot;SCOTT&quot;.&quot;ISEQ$$_258989&quot;.nextval
NAME
SALARY
COMMISSION
TOTAL           &quot;SALARY&quot;+&quot;COMMISSION&quot;

5 rows selected.

SQL&gt;

huangapple
  • 本文由 发表于 2023年7月10日 19:51:59
  • 转载请务必保留本文链接:https://go.coder-hub.com/76653459.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定