0

Spoiler: solution to this is here. the solution was really simple.

I'm trying to insert data from an external table.

Here's the code I'm trying to use:

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

But now i get this error:

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

Line 171 is the last line of this part

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

Which is clearly not a duplicate column.

If I do:

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

I get

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

Line 171 is ext.construct_id NUMBER (10),


Background:

The reason I'm listing the columns out instead of just saying insert into construct select is because I have a virtual column in the construct table taht's defined like this: construct VARCHAR2 (200) GENERATED ALWAYS AS (n_term || '-' || enz_name || '-' ||c_term || ' ' ||mutations) VIRTUAL, And there's no corresponding column in the csv file for it.

The second column in the ERD called CONSTRUCT is defined like this: enter image description here

Edit: originally, I defined datatypes for columns from the table that I was inserting into. This was a mistake and I deleted that code, replacing it with the new version for readability. It was taking up a lot of room in this post.

(Edit: originally, I defined datatypes for columns from the table that I was inserting into. This was a mistake and I deleted that code, replacing it with the new version for readability. It was taking up a lot of room in this post.)

0
3

If you used an IDE to develop your code, the IDE should show you exactly where you have an error.

SQL Developer shows that you have a syntax error. (magenta squiggly underline)

Screenshot of SQLDeveloper showing magenta squiggly underline where there is a syntax error

Remove the data type definitions from your first list of columns. (The data types are defined at the time of CREATE TABLE)

If you ever have a question about the syntax of something, always refer to the fine manual. In your case, the "data type" (in your first list of columns) is not part of the formal syntax. Therefore - you get an error.

Image from Documentation

Oracle syntax for insert_into_claues

1
  • Thank you so much. Weird, i'm using SQL developer too and I definitely didn't have "NUMBER" highlighted with a squiggly line. That would have made it easier. But now when delete the data types, and leave everything else the same, I get an error on the line that first mentions the "mw_kda" column. SQL Error: ORA-00957: duplicate column name. I've added to my post to show what i mean. Aug 9 '20 at 6:16

Not the answer you're looking for? Browse other questions tagged or ask your own question.