Oracle：插入表錯誤 - 列名重複或數據類型無效

我正在嘗試從外部表中插入數據。

INSERT /*+ ignore_row_on_dupkey_index ( consruct ( construct_id ) ) */
INTO construct 
     (construct_id,
     n_term ,
     enz_name,
     c_term,
     cpp,
     mutations,
     mw_kda)
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_input
   ACCESS PARAMETERS (
       RECORDS DELIMITED BY NEWLINE
       SKIP 1
       BADFILE bad_files:'badflie_insert_into_construct_from_construct.bad'
       FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
       MISSING FIELD VALUES ARE NULL 
       ) 
   LOCATION ('CONSTRUCT.CSV')
   REJECT LIMIT UNLIMITED) 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
   );

但現在我得到這個錯誤：

Error at Command Line : 171 Column : 7
Error report -
SQL Error: ORA-00957: duplicate column name
00957. 00000 -  "duplicate column name"

第 171 行是這部分的最後一行

INSERT /*+ ignore_row_on_dupkey_index ( consruct ( construct_id ) ) */
INTO construct 
     (construct_id,
     n_term ,
     enz_name,
     c_term,
     cpp,
     mutations,
     mw_kda)

這顯然不是重複的列。

如果我做：

INSERT /*+ ignore_row_on_dupkey_index ( consruct ( construct_id ) ) */
INTO construct 
   (construct_id, 
   n_term, 
   enz_name, 
   c_term, 
   cpp, 
   mutations, 
   mw_kda)
SELECT *
   FROM EXTERNAL ((
     ext.construct_id NUMBER (10),
     ext.n_term VARCHAR2 (50),
     ext.enz_name VARCHAR2 (50),
     ext.c_term VARCHAR2 (50),
     ext.cpp VARCHAR2 (50),
     ext.mutations VARCHAR2 (50),
     ext.mw_kda NUMBER (7,3))
 
 TYPE ORACLE_LOADER
   DEFAULT DIRECTORY data_to_input
   ACCESS PARAMETERS (
       RECORDS DELIMITED BY NEWLINE
       SKIP 1
       BADFILE bad_files:'badflie_insert_into_construct_from_construct.bad'
       FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
       MISSING FIELD VALUES ARE NULL 
       ) 
   LOCATION ('CONSTRUCT.CSV')
   REJECT LIMIT UNLIMITED) 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
   );

我明白了

Error at Command Line : 174 Column : 10
Error report -
SQL Error: ORA-00902: invalid datatype
00902. 00000 -  "invalid datatype"

第 171 行是ext.construct_id NUMBER (10),

外部表名是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
);

引用自：https://dba.stackexchange.com/questions/273483