oracle SQLLDR 控制文件：加载多个文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9229415/
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
SQLLDR control file: Loading multiple files
提问by user960740
Iam trying to load several data files into a single table. Now the files themselves have the following format:
我正在尝试将多个数据文件加载到一个表中。现在文件本身具有以下格式：
file_uniqueidentifier.dat_date
My control file looks like this
我的控制文件看起来像这样
LOAD DATA
INFILE '/home/user/file*.dat_*'
into TABLE NEWFILES
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(
FIRSTNAME CHAR NULLIF (FIRSTNAME=BLANKS)
,LASTNAME CHAR NULLIF (LASTNAME=BLANKS)
)
My SQLLDR on the other hand looks like this
另一方面，我的 SQLLDR 看起来像这样
sqlldr control=loader.ctl, userid=user/pass@oracle, errors=99999,direct=true
The error produced is SQL*Loader-500 unable to open file (/home/user/file*.dat_*) SQL*Loader-553 file not found
产生的错误是 SQL*Loader-500 无法打开文件 (/home/user/file*.dat_*) SQL*Loader-553 file not found
Does anyone have an idea as to how I can deal with this issue?
有没有人知道我如何处理这个问题？
回答by Adam Hawkes
SQLLDRdoes not recognize the wildcard. The only way to have it use multiple files to to list them explicitly. You could probably do this using a shell script.
SQLLDR不识别通配符。让它使用多个文件来明确列出它们的唯一方法。您可能可以使用 shell 脚本执行此操作。
回答by user2543390
Your file naming convention seem like you can combine those files in to one making that one being used by the sqlldr control file. I don't know how you can combine those files into one file in Unix, but in Windows I can issue this command
您的文件命名约定似乎可以将这些文件组合成一个，使 sqlldr 控制文件使用该文件。我不知道如何在 Unix 中将这些文件合并为一个文件，但在 Windows 中我可以发出此命令
copy file*.dat* file.dat
This command will read all the contents of the files that have the names that start with fileand extension of datand put in the file.datfile.
此命令将读取名称以file 开头和扩展名为dat的文件的所有内容，并将其放入file.dat文件。
回答by Kamran Umer
I have used this option and this works fine for multiple files uploading into single table.
我已经使用了这个选项，这适用于将多个文件上传到单个表中。
-- SQL-Loader Basic Control File
-- SQL-Loader 基本控制文件
options ( skip=1 )
load data
infile 'F:\oracle\dbHome\BIN\sqlloader\multi_file_insert\dept1.csv'
infile 'F:\oracle\dbHome\BIN\sqlloader\multi_file_insert\dept2.csv'
truncate into table scott.dept2
fields terminated by ","
optionally enclosed by '"'
( DEPTNO
, DNAME
, LOC
, entdate
)
