Duplicate Column Name Error While Using External Table. (Doc ID 2885298.1)

Last updated on JULY 20, 2024

Applies to:

Symptoms

Duplicate column name error while using External Table.
demo@PDB1> create table test_table( c1 number, c2 varchar2(60) );

Table created.

demo@PDB1> insert into test_table
2 select *
3 from external(
4 ( c1 number,
5 c2 char(10) )
6 type oracle_loader
7 default directory TEST_DIR
8 access parameters
9 ( records delimited by newline
10 nologfile
11 nobadfile
12 fields terminated by ','
13 (c1 recnum, c2 char(10))
14 )
15 location(TEST_DIR:'file1.txt')
16 reject limit unlimited ) ;

14 rows created.

demo@PDB1> rollback;

Rollback complete.

demo@PDB1> insert into test_table
2 select c1,c2
3 from external(
4 ( c1 number,
5 c2 char(10) )
6 type oracle_loader
7 default directory TEST_DIR
8 access parameters
9 ( records delimited by newline
10 nologfile
11 nobadfile
12 fields terminated by ','
13 (c1 recnum, c2 char(10))
14 )
15 location(TEST_DIR:'file1.txt')
16 reject limit unlimited ) ;

14 rows created.

demo@PDB1> rollback;

Rollback complete.

demo@PDB1> insert into test_table(c1,c2)
2 select c1,c2
3 from external(
4 ( c1 number,
5 c2 char(10) )
6 type oracle_loader
7 default directory TEST_DIR
8 access parameters
9 ( records delimited by newline
10 nologfile
11 nobadfile
12 fields terminated by ','
13 (c1 recnum, c2 char(10))
14 )
15 location(TEST_DIR:'file1.txt')
16 reject limit unlimited ) ;
insert into test_table(c1,c2)
*
ERROR at line 1:
ORA-00957: duplicate column name

Call Stack should contain qcpitextab_collist

Changes

 Upgrade to 21.3

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.