问 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, 
    mutations, 
    mw_kda)
SELECT 
      construct_id,
      n_term ,
      enz_name,
      c_term ,
      cpp ,
      mutations ,
      mw_kda
      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  
    -- 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, 
    mutations, 
    mw_kda)
SELECT 
      construct_id,
      n_term ,
      enz_name,
      c_term ,
      cpp ,
      mutations ,
      mw_kda
      construct_stg ext
WHERE NOT EXISTS (
        SELECT * FROM construct c