英文:
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 "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 Generated Class
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 Table
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 Generated Class
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; }
}
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> 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> insert into test_so (name, salary, commission, total)
2 values ('Little', 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> insert into test_so (name, salary, commission)
2 values ('Little', 100, 20);
1 row created.
SQL> select * from test_so;
ID NAME SALARY COMMISSION TOTAL
---------- ---------- ---------- ---------- ----------
1 Little 100 20 120
1 row selected.
SQL>
Therefore, check whether there's any virtual column in those tables. How? Like this:
SQL> SELECT column_name, data_default
2 FROM user_tab_columns
3 WHERE table_name = 'TEST_SO';
COLUMN_NAME DATA_DEFAULT
--------------- ----------------------------------------
ID "SCOTT"."ISEQ$$_258989".nextval
NAME
SALARY
COMMISSION
TOTAL "SALARY"+"COMMISSION"
5 rows selected.
SQL>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论