3

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!

Adil
  • 111
  • 3
  • 11

1 Answers1

2

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
)
Vamsi Prabhala
  • 48,685
  • 4
  • 36
  • 58
  • Thanks, I went with your suggestion and it is still not working for me. I get the following error message: SQL*Loader-404: Column present more than once in R2X1.RDA_PROD_CNTG's INTO TABLE block. – Adil Dec 30 '16 at 22:55
  • 1
    if this is the script you are using, i don't see any column specified more than once. – Vamsi Prabhala Dec 30 '16 at 23:02
  • Ok caught the issue, I changed the column names from 2 to filler2 and so on per your suggestion. I guess it wasn't accepting a number as a column name. Thank you! – Adil Dec 30 '16 at 23:09