nad nad - 2 months ago 19
ASP.NET (C#) Question

Not able to insert values in history table which has (SEQUENCE_NO) as Column

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: not enough values


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


Script of the table

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


)

Answer

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?

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

But keep in mind order of columns