SQL Loader input file more columns than table

Question

I was wondering how do I handle the fact that my input file has 26 columns and the table that the file will be loaded to only has 6 columns, of which some columns values will needed to be hardcoded using the CONSTANT keyword and skipping columns by using FILLER.

For some reason, I am not able to do so. Below is the control file:

LOAD DATA 
TRUNCATE 
INTO TABLE R2X1.RDA_PROD_CNTG
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '\"'
(
ITEM, 
GLOBAL_DOMAIN_TYPE CONSTANT S
GLOBAL_ID CONSTANT 10
GLOBAL_DOMAIN_DESC CONSTANT 'Sales Forecast',
5 FILLER,
6 FILLER,
7 FILLER,
8 FILLER,
9 FILLER,
10 FILLER,
11 FILLER,
12 FILLER,
13 FILLER,
14 FILLER,
15 FILLER,
16 FILLER,
17 FILLER,
18 FILLER,
19 FILLER,
20 FILLER,
DOMAIN_ID,
DOMAIN_LABEL,
23 FILLER,
24 FILLER,
25 FILLER, 
26 FILLER
)

The table that will the file will be loaded to has the following definition:

ITEM                VARCHAR2(25 BYTE),
GLOBAL_DOMAIN_TYPE  VARCHAR2(1 BYTE),
GLOBAL_ID           NUMBER(3),
GLOBAL_DOMAIN_DESC  VARCHAR2(50 BYTE),
DOMAIN_ID           NUMBER(3),
DOMAIN_LABEL        VARCHAR2(20 BYTE)

Any help will be greatly appreciated!

Answer 1

Because some of the columns are constants and there are 26 columns in the file, you were missing 3 of the fillers. Include them and this should work.

LOAD DATA 
--INFILE option missing. I'm not sure if you excluded it from the question
TRUNCATE 
INTO TABLE R2X1.RDA_PROD_CNTG
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '\"'
(
ITEM, 
GLOBAL_DOMAIN_TYPE CONSTANT 'S', --missing quotes and comma
GLOBAL_ID CONSTANT 10, --missing comma
GLOBAL_DOMAIN_DESC CONSTANT 'Sales Forecast',
filler2 FILLER,
filler3 FILLER,
filler4 FILLER,
filler5 FILLER,
filler6 FILLER,
filler7 FILLER,
filler8 FILLER,
filler9 FILLER,
filler10 FILLER,
filler11 FILLER,
filler12 FILLER,
filler13 FILLER,
filler14 FILLER,
filler15 FILLER,
filler16 FILLER,
filler17 FILLER,
filler18 FILLER,
filler19 FILLER,
filler20 FILLER,
DOMAIN_ID,
DOMAIN_LABEL,
filler23 FILLER,
filler24 FILLER,
filler25 FILLER, 
filler26 FILLER
)