如何使用SQL Loader将单列多值拆分为多行加载至数据库表？

问题背景 #

需要将包含多值渠道伙伴的数据文件通过SQL Loader加载到数据库表，要求把单行中的多个渠道伙伴拆分成独立行，同时用序列生成主键ROW_ID。

数据文件格式：

IDENTIFY|CHANNEL_NAME|CHANNEL_PARTNERS                                                  |LOAD_DATE
1-ED    |WEBSITE     |"redbus","abhibus","amazon travel","irctc"                        |02-FEB-2022
1-LP    |WALKIN      |"physical reservation","printed reservation","current reservation"|04-FEB-2022

期望加载后的数据格式：

IDENTIFY   CHANNEL_NAME   CHANNEL_PARTNERS     LOAD_DATE
1-ED       WEBSITE        redbus               02-FEB-2022
1-ED       WEBSITE        abhibus              02-FEB-2022
1-ED       WEBSITE        amazon travel        02-FEB-2022
1-ED       WEBSITE        irctc                02-FEB-2022
1-LP       WALKIN         physical reservation 04-FEB-2022
1-LP       WALKIN         printed reservation  04-FEB-2022
1-LP       WALKIN         current reservation  04-FEB-2022

现有CTL文件无法实现多值字段拆分，需进行修改。

解决方案 #

通过Oracle字符串函数结合SQL Loader的序列、条件加载逻辑，实现多值字段拆分。具体修改如下：

1. 最终CTL文件 #

load data
infile 'mchannel.txt'
append into table master_channel
fields terminated by "|"
trailing nullcols
(
    identify,
    channel_name,
    channel_partners_raw boundfiller, -- 临时存储原始多值字段，不加载到表
    load_date,
    row_id "chan_seq.nextval",
    -- 按行号拆分带引号的渠道伙伴，提取引号内内容
    channel_partners "REGEXP_SUBSTR(:channel_partners_raw, '\"([^\"]+)\"', 1, :rec_num, 'i', 1)",
    rec_num sequence(1,1) -- 为原始行生成递增行号，用于拆分定位
)
-- 仅加载拆分出有效内容的行，避免空行
when (REGEXP_SUBSTR(:channel_partners_raw, '\"([^\"]+)\"', 1, :rec_num, 'i', 1) is not null)

2. 关键逻辑说明 #

• boundfiller：标记channel_partners_raw为临时字段，仅读取数据文件中的原始多值内容，不直接写入目标表。
• rec_num sequence(1,1)：为每一条原始数据生成从1开始的递增行号，用于指定拆分时提取第几个渠道伙伴值。
• REGEXP_SUBSTR正则拆分：通过正则表达式'\"([^\"]+)\"'匹配带双引号的伙伴名称，最后一个参数1返回引号内的纯文本（去掉引号），:rec_num指定当前要提取的第N个值。
• when条件过滤：确保只有拆分出有效伙伴名称时才生成数据行，避免因拆分到空值而产生无效记录。

3. 前置准备 #

• 提前创建主键序列：CREATE SEQUENCE chan_seq START WITH 1 INCREMENT BY 1;
• 若数据库日期格式与数据文件不匹配，需在load_date字段后添加格式转换，例如：load_date "TO_DATE(:load_date, 'DD-MON-YYYY')"

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