无法在(SEQUENCE_NO)作为列的历史记录表中插入值 [英] Not able to insert values in history table which has (SEQUENCE_NO) as Column

问题描述

我想要的是将数据插入到主表中.我首先将其旧数据插入其历史记录表.历史记录表的主表有两列，分别是HISTSEQ_NO和HIST_DATE.

What I want is, while inserting the data into the main table. I am first inserting its old data to its history table. The history table has more 2 columns of what main table has, which is HISTSEQ_NO and HIST_DATE.

因此，在插入下面的代码时.我收到错误消息

So, while inserting with my below code. I am getting error as

ORA-00947:值不足

ORA-00947: not enough values

因此，如何处理SEQUENCE部分.请帮助我，因为我不是Oracle冠军.

So, how to handle the SEQUENCE part. Kindly help me as I am not a Oracle champ.

string queryInsert;
        queryInsert = "insert into xxacl_pN_LEASES_ALL_h select sysdate, t.* from xxacl_pN_LEASES_ALL t";
        OracleConnection conInsert = new OracleConnection(System.Configuration.ConfigurationManager.ConnectionStrings["OracleConn"].ToString());
        OracleCommand cmd1 = new OracleCommand();
        string allQueries = queryInsert;
        cmd1.CommandText = allQueries;
        cmd1.Connection = conInsert;
        conInsert.Open();
        cmd1.ExecuteNonQuery();

表格脚本

CREATE TABLE XXCUS.XXACL_PN_LEASES_ALL_H
    (
      HISTSEQ_NO                   NUMBER           NOT NULL,
      HIST_DATE                    DATE             NOT NULL,
      MKEY                         NUMBER,
      LEASE_ID                     NUMBER,
      LAST_UPDATE_DATE             DATE             NOT NULL,
      LAST_UPDATED_BY              NUMBER           NOT NULL,
      CREATION_DATE                DATE             NOT NULL,
      CREATED_BY                   NUMBER           NOT NULL,
      LAST_UPDATE_LOGIN            NUMBER,
      LEASE_NUM                    VARCHAR2(30 BYTE) NOT NULL,
      PARENT_LEASE_ID              NUMBER,
      ADDRESS_LOCATION_ID          NUMBER,
      LEASE_TYPE_CODE              VARCHAR2(30 BYTE) NOT NULL,
      PAYMENT_TERM_PRORATION_RULE  NUMBER,
      ABSTRACTED_BY_USER           NUMBER           NOT NULL,
      COMMENTS                     VARCHAR2(240 BYTE),
      STATUS                       VARCHAR2(1 BYTE),
      ORG_ID                       NUMBER(15)       DEFAULT NULL,
      LEASE_CLASS_CODE             VARCHAR2(30 BYTE) NOT NULL,
      LEASE_STATUS                 VARCHAR2(30 BYTE) NOT NULL,
      CUSTOMER_ID                  NUMBER(15),
      DELETE_FLAG                  CHAR(1 BYTE),
      PROJECT_ID                   NUMBER,
      BUILDING_ID                  NUMBER,
      FLOOR_ID                     NUMBER,
      FLAT_ID                      NUMBER,
      CARPET_AREA                  VARCHAR2(30 BYTE),
      SALEABLE_AREA                VARCHAR2(30 BYTE),
      FLAT_TYPE                    VARCHAR2(30 BYTE),
      FLAT_STATUS                  VARCHAR2(30 BYTE),
      FLAT_SUBSTATUS               VARCHAR2(30 BYTE),
      CEF_MKEY                     NUMBER,
      BOOKING_NO                   NUMBER,
      ASSIGNED_TO                  NUMBER,
      APPROVER_LAVEL               NUMBER,
      PDC_TYPE                     VARCHAR2(30 BYTE),
      IS_MIGRATED                  CHAR(1 BYTE),
      SYS_CREATION_DATE            DATE,
      F_UPDATED_BY                 NUMBER,
      F_FOLLOWUP_DATE              DATE,
      F_ACTIVITY_ID                NUMBER,
      F_SUB_ACTIVITY_ID            NUMBER,
      F_FOLLOWUP_TYPE_ID           NUMBER,
      F_NEXT_FOLLOW_UP_DATE        DATE,
      F_NEXT_ACTIVITY_ID           NUMBER,
      F_NEXT_SUB_ACTIVITY_ID       NUMBER,
      F_REMARKS                    VARCHAR2(500 BYTE),
      F_FOLLOWUP_SR_NO             NUMBER,
      F_REASSIGN_REASON            NUMBER(10),
      USER_TYPE                    VARCHAR2(10 BYTE),
      LOCATION_ID                  NUMBER(10),
      F_LAST_UPDATE_DATE           DATE,
      F_TASK_ID                    NUMBER(10),
      F_TASK_SR_NO                 NUMBER(10),
      BOOKING_DATE                 DATE,
      INV_DATE                     DATE,
      LOAN_DETAILS                 VARCHAR2(10 BYTE),
      AUTO_GEN_BILLING             VARCHAR2(10 BYTE),
      ADF                          VARCHAR2(10 BYTE),
      SALES_USER_ID                NUMBER(10),
      PREDEFINED_CUST              CHAR(1 BYTE),
      SCHEME_ID                    NUMBER(10),
      LOCK_PERIOD                  NUMBER(10),
      NO_OF_DAYS                   NUMBER(10),
      TYPE_OF_BOOKING              VARCHAR2(10 BYTE),
      RENTFREE_PERIOD              DATE,
      FREE_NO_DAYS                 VARCHAR2(250 BYTE),
      LOI_DATE                     DATE

)

推荐答案

如我所见，您已经为另外一列HIST_DATE添加了值sysdate.现在，您需要为第二列HISTSEQ_NO添加值.您是否有表xxacl_pN_LEASES_ALL_h的序列，或者您可以使用其他序列?

As I see you already added values sysdate for one additional column HIST_DATE. Now you need add value for secondary column HISTSEQ_NO. Do you have sequence for table xxacl_pN_LEASES_ALL_h or may be you have another sequence which you can use?

例如，如果您有序列my_seq，则可以在查询中使用它:

For example, if you have sequence my_seq, you can use it in your query:

insert into xxacl_pN_LEASES_ALL_h 
    select 
        my_seq.nextval, sysdate, t.* 
    from 
        xxacl_pN_LEASES_ALL t

但是请记住列的顺序

这篇关于无法在(SEQUENCE_NO)作为列的历史记录表中插入值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！