SQL Loader 输入文件的列多于表

Question

我想知道如何处理这样一个事实，即我的输入文件有 26 列，而文件将加载到的表只有 6 列，其中一些列值需要使用 CONSTANT 关键字进行硬编码并跳过使用 FILLER 列。

出于某种原因，我无法这样做。下面是控制文件:

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
)

文件将加载到的表具有以下定义:

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)

任何帮助将不胜感激!

Answer 1

因为一些列是常量并且文件中有 26 列，所以您缺少 3 个填充项。包括它们，这应该有效。

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
)