从 SQL*Loader 控制文件中访问数据文件名

Question

如何从 SQL*Loader 控制文件中访问输入数据文件名，以便可以将其与输入文件中的数据一起插入表中？

假设我有以下控制文件：

LOAD DATA

APPEND
INTO TABLE STG_AM02_BA_RAW
WHEN (1:2) = 'DT'
(
        SUBSCRIBER_NO                   POSITION(11:18)CHAR, 
        ACCOUNT_NO                      POSITION(19:32)CHAR, 
        SUBSCRIBER_NAME                 POSITION(33:92)CHAR
)

我想做类似的事情：

LOAD DATA

APPEND
INTO TABLE STG_AM02_BA_RAW
WHEN (1:2) = 'DT'
(
        SUBSCRIBER_NO                   POSITION(11:18)CHAR, 
        ACCOUNT_NO                      POSITION(19:32)CHAR, 
        SUBSCRIBER_NAME                 POSITION(33:92)CHAR, 
        INPUTFILE                       INPUTFILENAME()CHAR
)

假设我无权编辑将使用此控制文件调用 SQL*Loader 的 shell 脚本。

Answer 1

从 11g 开始，无法直接从 SQL*Loader 控制文件访问文件名。

您基本上必须从脚本环境中处理它。

如果您无法修改加载脚本，也许您可​​以向数据文件添加标头记录？

看起来您在位置 1:2 有一个记录类型字段 - 您可以修改数据文件创建以包含文件名记录类型吗？

例如，“FN”数据类型：

FN                ...        inputfile.txt
DT     12345678XXX...XXXXXYYYYYYYYYYYYYYYY
DT     12345678XXX...XXXXXYYYYYYYYYYYYYYYY
DT     12345678XXX...XXXXXYYYYYYYYYYYYYYYY
DT     12345678XXX...XXXXXYYYYYYYYYYYYYYYY
DT     12345678XXX...XXXXXYYYYYYYYYYYYYYYY

您的加载脚本可以更改为：

LOAD DATA

APPEND
INTO TABLE STG_AM02_BA_RAW
WHEN (1:2) = 'FN'
(
        INPUTFILE                       POSITION(1:92)CHAR
)
WHEN (1:2) = 'DT'
(
        SUBSCRIBER_NO                   POSITION(11:18)CHAR, 
        ACCOUNT_NO                      POSITION(19:32)CHAR, 
        SUBSCRIBER_NAME                 POSITION(33:92)CHAR
)

一切取决于您是否可以更新数据文件...

例如，

echo "FNinputfile.txt" > header.txt
cat header.txt inputfile.txt > newinputfile.txt

如果您需要针对每个数据行引用文件名，则可以加载数据到多个临时表中：

LOAD DATA
TRUNCATE INTO TABLE STAGE_FILENAME
WHEN (1:2) = 'FN'
(
        INPUTFILE                       POSITION(1:92)CHAR
)
TRUNCATE INTO TABLE STAGE_DATA
WHEN (1:2) = 'DT'
(
        SUBSCRIBER_NO                   POSITION(11:18)CHAR, 
        ACCOUNT_NO                      POSITION(19:32)CHAR, 
        SUBSCRIBER_NAME                 POSITION(33:92)CHAR
)

...并使用 SQL 将它们连接在一起：

insert into STG_AM02_BA_RAW
    (
    subscriber_no,
    account_no,
    subscriber_name,
    input_filename
    )
select
    d.subscriber_no,
    d.account_no,
    d.subscriber_name,
    f.inputfile
from
    stage_data d,
    inputfile d

如果有并发负载，则此过程会失败。

您在评论中说您可以更改数据文件 - 您能否将文件更改为将文件名附加到每条记录中？如果是这样，问题就会消失。你只需要包括：

    SUBSCRIBER_NAME                 POSITION(92:*)CHAR

Answer 2

我认为在您指定的情况下没有办法做到这一点，据我所知，无法正确引用“数据”部分中的文件名。

解决方法的几个想法：

• 使用单独的 SQL 语句更新新插入的记录。您也许能够从调用 SQL*Loader 的批处理文件构建语句。
• 修改数据文件以包含文件名（同样，可以从批处理文件完成）。

• 让批处理文件构建控制文件以将文件名作为常量包含在内，这样您就可以得到类似的内容

  INPUTFILE CONSTANT "my_data.dat"

内容希望这会有所帮助。

Answer 3

解决此问题的简单方法是在每个记录的末尾添加一个带有文件名的额外列，并将该列位置映射到字段。

Answer 4

我知道这已经太老了，但我仍然在 2023 年到达这里，而且仍然没有答案，所以这就是我的做法。

我修改了 .ctl 文件以使用通用文件名进行输入：

INFILE '[path to data files]\myfile.dat'

然后，我创建了一个带有 DO 循环的批处理文件来逐步遍历所有数据文件名：

FOR %%X in ("[path to data files]\*.[your extension]") DO (
copy /Y %%~dpnX [path to batch files]\myfile.dat
[path to your batch file that runs sqlldr]\load_my_data.bat
)

我加载了 1218 个数据文件和大约 10-125 万行多一点的数据文件。 15分钟。