oracle 变量可以传递给 SQL*Loader 控制文件吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3760283/
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
Can variables be passed to a SQL*Loader control file?
提问by Adam Paynter
Suppose you have a table:
假设你有一张桌子：
CREATE TABLE Customer
(
batch_id NUMBER,
customer_name VARCHAR2(20),
customer_address VARCHAR2(100)
)
And suppose you have a control file to populate this table:
并假设您有一个控制文件来填充此表：
LOAD DATA INFILE 'customers.dat' REPLACE INTO TABLE Customer ( batch_id ??????, customer_name POSITION(001:020), customer_address POSITION(021:120) )
Is it possible to pass a value for batch_idto my control file when I run SQL*Loader? For example, is it possible to specify a bind variable (turning the question marks into :MY_AWESOME_BATCH_ID)?
batch_id当我运行 SQL*Loader 时，是否可以将值传递给我的控制文件？例如，是否可以指定绑定变量（将问号变成:MY_AWESOME_BATCH_ID）？
回答by Erich Kitzmueller
A relatively easy way to archive that is to create a stored function that returns the batch number and use it in the loader file.
一种相对简单的归档方法是创建一个存储函数，该函数返回批号并在加载程序文件中使用它。
create or replace function getBatchNumber return number as
begin
return 815;
end;
/
LOAD DATA INFILE 'customers.dat'
REPLACE
INTO TABLE Customer
(
batch_id "getBatchNumber",
customer_name POSITION(001:020),
customer_address POSITION(021:120)
)
回答by Jim Hudson
Not easily, if I remember right, but here are a couple of alternatives:
如果我没记错的话，这并不容易，但这里有几个选择：
- If there's only going to be one process running SQLLoader at a time, use nulls or a fixed value and then run a SQLPlus script as part of the process afterwards to do the update to a sequence value.
- Call a script which will grab the next sequence value for your batch ID and then spool out the control file, including the batch_id constant.
- 如果一次只有一个进程运行 SQL Loader，请使用空值或固定值，然后运行 SQLPlus 脚本作为进程的一部分，以更新序列值。
- 调用一个脚本，它将为您的批次 ID 获取下一个序列值，然后假脱机输出控制文件，包括 batch_id 常量。
回答by Vitali Tchalov
If it's acceptable to have BATCH_ID values generated automatically by incrementing on each load, than this worked for me. The 10 minutes interval in the sample would need to be adjusted to the specific load - to be accurate, the loading must complete within the specified interval and the next loading must not be started in less than time specified.
如果通过每次加载递增自动生成 BATCH_ID 值是可以接受的，那么这对我有用。样品中的 10 分钟间隔需要根据特定负载进行调整——准确地说，加载必须在指定的间隔内完成，并且下一次加载不得在指定的时间内开始。
A drawback is that it slows down noticeably on large volumes - that's the price running the MAX aggregate on every line.
一个缺点是它在大量运行时明显减慢 - 这是在每条线上运行 MAX 聚合的价格。
LOAD DATA
...
INTO TABLE XYZ
(
...
BATCH_ID expression "(select nvl(max(batch_id) + 1, 1) from extra_instruments_party_to where create_date < (sysdate - interval '10' minute))",
CREATE_DATE SYSDATE
)
