Oracle外部表加载时如何拒绝包含多余列的数据文件？

问题说明 #

创建了Oracle外部表TESTING_DUMP，定义了FIELD_1、FIELD_2、FIELD_3三个字段，要求加载的数据文件仅包含3列。当前现象：

• 文件列数不足3列时，加载失败（符合预期）；
• 文件列数超过3列时，加载不会失败，仅加载前3列（不符合需求）。

需要添加逻辑，让文件存在多余列时加载失败。

当前创建语句：

CREATE TABLE TESTING_DUMP (
"FIELD_1"            NUMBER,
"FIELD_2"            VARCHAR2(5),
"FIELD_3"            VARCHAR2(5)
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY MY_DIR
ACCESS PARAMETERS
(
  RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
  BADFILE "MY_DIR":"TEST.bad"
  LOGFILE "MY_DIR":"TEST.log"
  READSIZE 1048576
  FIELDS TERMINATED BY "|" LDRTRIM
  MISSING FIELD VALUES ARE NULL
  REJECT ROWS WITH ALL NULL FIELDS
  (
  "LOAD"               CHAR(1),
  "FIELD_1"            CHAR(5),
  "FIELD_2"            INTEGER EXTERNAL(5),
  "FIELD_3"            CHAR(5)
  )
  )
  location
  (
  'Test.xls'
  )
)REJECT LIMIT 0;

测试文件Test.xls内容：

|11111|22222|33333|AAAAA
|22222|33333|44444|

注：第一行含4列（多余1列）但未被拒绝，第二行正常。

解决方案 #

通过新增捕获多余内容的字段，并添加拒绝条件，实现多余列时加载失败。修改后的创建语句如下：

CREATE TABLE TESTING_DUMP (
"FIELD_1"            NUMBER,
"FIELD_2"            VARCHAR2(5),
"FIELD_3"            VARCHAR2(5)
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY MY_DIR
ACCESS PARAMETERS
(
  RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
  BADFILE "MY_DIR":"TEST.bad"
  LOGFILE "MY_DIR":"TEST.log"
  READSIZE 1048576
  FIELDS TERMINATED BY "|" LDRTRIM
  MISSING FIELD VALUES ARE NULL
  REJECT ROWS WITH ALL NULL FIELDS
  REJECT ROWS WHEN TRAILING_DATA IS NOT NULL -- 新增：存在多余内容则拒绝该行
  (
  "LOAD"               CHAR(1),
  "FIELD_1"            CHAR(5),
  "FIELD_2"            INTEGER EXTERNAL(5),
  "FIELD_3"            CHAR(5),
  TRAILING_DATA        CHAR(4000) -- 新增：捕获业务字段后的所有剩余内容
  )
)
LOCATION
(
  'Test.xls'
)
REJECT LIMIT 0;

逻辑说明 #

1. TRAILING_DATA字段：用于接收每行中FIELD_1、FIELD_2、FIELD_3之后的所有剩余内容（包括多余的分隔符和列值），长度可根据实际情况调整，避免截断导致误判。
2. REJECT ROWS WHEN TRAILING_DATA IS NOT NULL：当该行存在多余内容时，将其标记为无效行并写入BAD文件；由于设置了REJECT LIMIT 0，一旦出现此类无效行，整个加载过程会直接失败。

内容的提问来源于stack exchange，提问作者Manish