Oracle：插入表错误 - 列名重复或数据类型无效

外部表名是construct_ext

CREATE TABLE construct_ext
  (    construct_id NUMBER (10),
      n_term VARCHAR2 (50),
      enz_name VARCHAR2 (50),
      c_term VARCHAR2 (50),
      cpp VARCHAR2 (50),
      mutations VARCHAR2 (50),
      mw_kda NUMBER (7,3)
  )
  ORGANIZATION EXTERNAL
  (
    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY data_to_input
   
    ACCESS PARAMETERS 
  (
        RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII 
        TERRITORY AMERICA
        SKIP 1
        BADFILE bad_files:'badflie_insert_into_construct_from_construct.bad'
        LOGFILE bad_files:'logflie_insert_into_construct_from_construct.log'
        FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
        MISSING FIELD VALUES ARE NULL 
        ) 
    LOCATION ('CONSTRUCT.CSV')
   ) REJECT LIMIT UNLIMITED;
    

INSERT /*+ ignore_row_on_dupkey_index ( consruct ( construct_id ) ) */
INTO construct 
    (construct_id, 
    n_term, 
    enz_name, 
    c_term, 
    cpp, 
    mutations, 
    mw_kda)
SELECT 
      construct_id,
      n_term ,
      enz_name,
      c_term ,
      cpp ,
      mutations ,
      mw_kda
FROM
      construct_ext ext
  
WHERE NOT EXISTS (
        SELECT * FROM construct c
        WHERE c.n_term = ext.n_term
        AND c.enz_name = ext.enz_name
        AND c.c_term = ext.c_term
        AND c.cpp = ext.cpp
        AND c.mutations = ext.mutations
);

编辑：-创建外部表后验证您是否能够查询该表

编辑：-根据@a_horse_with_no_name，您可以访问外部表，而无需为数据库版本 18c 及更高版本创建使用内联外部表

当您尝试在不创建外部表的情况下插入目标表时出现问题（希望有人能对此有所了解）解决方法是创建临时表然后插入目标表

    CREATE TABLE construct_stg  
    AS
    -- select part works without creating external table 
    SELECT *
        FROM EXTERNAL (
        (
          construct_id NUMBER(10),
          n_term VARCHAR2 (50),
          enz_name VARCHAR2 (50),
          c_term VARCHAR2 (50),
          cpp VARCHAR2 (50),
          mutations VARCHAR2 (50),
          mw_kda NUMBER (7,3)
          )
        TYPE ORACLE_LOADER
        DEFAULT DIRECTORY data_to_import 
        ACCESS PARAMETERS (
            RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
            TERRITORY AMERICA
            SKIP 1
            BADFILE bad_files:'badflie_insert_into_construct_from_construct.bad'
            --LOGFILE bad_files:'logflie_insert_into_construct_from_construct.log'--create different directory for log file or skip this line 
            FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
            MISSING FIELD VALUES ARE NULL
            (
              construct_id,
              n_term ,
              enz_name,
              c_term ,
              cpp ,
              mutations ,
              mw_kda 
              )
            )
    
        LOCATION ('CONSTRUCT.CSV')
        REJECT LIMIT UNLIMITED)ext_tab;
 
   CREATE INDEX idx_construct_id ON construct_stg(construct_id);
 
   ALTER TABLE construct_stg ADD CONSTRAINT pk_construct_id_stg PRIMARY KEY (construct_id) USING INDEX;  -- to enforce unique or skip this



    INSERT /*+ ignore_row_on_dupkey_index ( consruct,idx_construct_id) */
    INTO construct 
    (construct_id, 
    n_term, 
    enz_name, 
    c_term, 
    cpp, 
    mutations, 
    mw_kda)
SELECT 
      construct_id,
      n_term ,
      enz_name,
      c_term ,
      cpp ,
      mutations ,
      mw_kda
FROM
      construct_stg ext
  
WHERE NOT EXISTS (
        SELECT * FROM construct c
        WHERE c.n_term = ext.n_term
        AND c.enz_name = ext.enz_name
        AND c.c_term = ext.c_term
        AND c.cpp = ext.cpp
        AND c.mutations = ext.mutations
);