Oracle:插入表错误-重复列名或无效数据类型开发者社区

link之家

链接快照平台

输入网页链接，自动生成快照
标签化管理网页链接

相关文章推荐

兴奋的草稿本 · 误操作恢复的最佳实践_云原生数据库 ...· 1 年前 ·

博学的保温杯 · 新一代Spring ...· 2 年前 ·

会开车的煎饼 · esxi主机打开虚拟机电源卡死_51CTO博客· 2 年前 ·

火星上的楼梯 · MYSQL UPDATE with IN ...· 2 年前 ·

淡定的斑马 · 将 CSV 文件转换为 Excel 工作簿 ...· 2 年前 ·

INSERT /*+ ignore_row_on_dupkey_index ( consruct ( construct_id ) ) */
INTO construct 
      (construct_id,
      n_term ,
      enz_name,
      c_term,
      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"

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

INSERT /*+ ignore_row_on_dupkey_index ( consruct ( construct_id ) ) */
INTO construct 
    (construct_id, 
    n_term, 
    enz_name, 
    c_term, 
    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"

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

    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

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

回答 1

Database Administration用户

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