oracle 从 SQL*Loader 控制文件中访问数据文件名
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1699574/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Accessing data filename from within SQL*Loader control file
提问by Lukman
How do I access the input data file name from within SQL*Loader control file so that I can insert it into the table along with data from the input file?
如何从 SQL*Loader 控制文件中访问输入数据文件名，以便将其与输入文件中的数据一起插入表中？
Let's say for example I have the following control file:
例如，假设我有以下控制文件：
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
)
I want to do something like:
我想做类似的事情：
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
)
Assume that I don't have access nor permission to edit the shell script that will invoke SQL*Loader with this control file.
假设我没有访问权限，也没有权限编辑将使用此控制文件调用 SQL*Loader 的 shell 脚本。
采纳答案by Nick Pierpoint
As of 11g, it isn't possible to access the filename directly from the SQL*Loader control file.
从 11g 开始，无法直接从 SQL*Loader 控制文件访问文件名。
You basically have to handle it from your scripting environment.
您基本上必须从脚本环境中处理它。
If you're not able to modify the loading script, perhaps you could add a header record to the datafile?
如果您无法修改加载脚本，也许您可以向数据文件添加标题记录？
It looks like you have a record type field in position 1:2 - can you modify the datafile creation to include a filename record type?
看起来您在 1:2 位置有一个记录类型字段 - 您可以修改数据文件创建以包含文件名记录类型吗？
For example, a "FN" data type:
例如，“FN”数据类型：
FN ... inputfile.txt
DT 12345678XXX...XXXXXYYYYYYYYYYYYYYYY
DT 12345678XXX...XXXXXYYYYYYYYYYYYYYYY
DT 12345678XXX...XXXXXYYYYYYYYYYYYYYYY
DT 12345678XXX...XXXXXYYYYYYYYYYYYYYYY
DT 12345678XXX...XXXXXYYYYYYYYYYYYYYYY
Your load script could then change to:
然后您的加载脚本可以更改为：
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
)
All depends if you can update the data file...
一切都取决于您是否可以更新数据文件...
For example,
例如，
echo "FNinputfile.txt" > header.txt
cat header.txt inputfile.txt > newinputfile.txt
If you need to reference the filename against each data row, you can load the data into multiple staging tables:
如果您需要针对每个数据行引用文件名，您可以将数据加载到多个临时表中：
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
)
... and join them together using SQL:
...并使用 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
This process falls over if you have concurrent loads.
如果您有并发负载，这个过程就会失败。
You said in the comments that you can change the data file - could you get the file changed to that the filename is appended to each record? If so, makes the issue go away. You'd just have to include:
您在评论中说您可以更改数据文件 - 您能否将文件更改为将文件名附加到每条记录？如果是这样，使问题消失。你只需要包括：
SUBSCRIBER_NAME POSITION(92:*)CHAR
回答by Thorsten
I don't think that there is a way to this in the circumstances you specified, AFAIK there is no way to properly reference the filename in the "data" part.
我认为在您指定的情况下没有办法做到这一点，AFAIK 无法在“数据”部分正确引用文件名。
Couple of ideas for a workaround:
解决方法的几个想法：
- Update the newly inserted records with a separate SQL statement. You might be able to build the statement from the batch file that invokes SQL*Loader.
- Modify the data file to include the filename (again, might be done from the batch file).
Have the batch file build the control file to include the filename as a constant, so you could have something like
INPUTFILE CONSTANT "my_data.dat"
- 使用单独的 SQL 语句更新新插入的记录。您或许能够从调用 SQL*Loader 的批处理文件构建语句。
- 修改数据文件以包含文件名（同样，可以从批处理文件中完成）。
让批处理文件构建控制文件以将文件名作为常量包含在内，因此您可以拥有类似
输入文件常量“my_data.dat”
Hope this helps.
希望这可以帮助。
回答by Nash
The easy way to tackle this issue is by adding an extra column to the end of the each record with the filename and map that column position to the field.
解决这个问题的简单方法是在每条记录的末尾添加一个额外的列，并将该列位置映射到该字段。
