Oracle SQLLDR Command

Today other people's storage code, see really a bit dizzy, finally read to know is using the SQLLDR command. Hey... Or not fine ah, the future is still more efforts.

Summary Kazakhstan SQLLDR command: Although most of them are online, they have to have experience! Open source is good.

Sqlldr is a command under CMD that is used to import text-formatted data into a database.
Command format:
Sqlldr Username/[email protected] Control=*.ctl

The *.CTL format is:
Load data
InFile "D://test.txt" External data file
InFile "D://test1.txt" to specify multiple data files
Append into table test appends data to tables
Fields terminated by "," the data of the external file is separated by ","
Optionally enclosed by ' "' section fields can be wrapped in double quotes
Trailingnullcols a field in a table does not have a corresponding value when a null value is filled
(
ID integerexternal, integer external indicates that the inserted data is a string, and if only the integer is retained, the inserted data is a binary
Name "Upper (: Name)" To convert the inserted value to uppercase
Con ": id| |:name", the value of the Con column in the table is the combined value of ID and name
Dtdate "YYYY-MM-DD" Insert date type data
)
Description
Load data
Tell Sqlldr what to do (in this case, indicate that you want to load the data). Sqlldr can also execute continue_load, that is, continue loading. This option is only available if you continue with a multi-table direct path load
INFILE *: If it is an * number, this will tell Sqlldr that the data to be loaded is actually contained in the control file itself, as shown in the next article. Specify the file name of another file that contains data, as in the previous example. If you prefer, you can overwrite this infile statement with a command-line argument. Be careful, command-line options always cover control file settings
The location of the append can also be used with one of the values in the following list:
Insert inserts a value into the table, but requires the table to be empty at the beginning, the default load option
Replacedelete the data in the table, and then inserts the new value, recording the time is slow
Append appending data to a table
Truncatetrunctate table, then insert new value, cannot fallback
Fields TERMINATED by ', ': Tell Sqlldr that the form of the data should be a comma-separated value. There are dozens of ways to describe the input data for SQLLDR, which is one of the more common methods.
TERMINATED Bywhitespace parses the string, finds the first occurrence of a whitespace character (tab, space, and line break), and then continues to find until the next non-whitespace character is found.
(ID integer external,
.....
Dtdate "Yyyy-mm-dd"): Tells Sqlldr the columns to load, the order of the columns in the input data, and the data type. This refers to the data type of the data in the input stream, not the data type in the database. The data type of the column defaults to char (255)

When you load such bound data, you most likely want to escape some of the columns in the input record. For example, you might load fields 1, 3, and 5, and skip the 2nd and 4th columns. To do this, SQLLDR provides the filler keyword. This allows you to map a column in an input record, but not put it in the database.


Cases:
C:\>sqlldr Scott/tiger Control=d:/dept.ctllog=d:/sqlldr.log

The contents of Dept.ctl are as follows:

Load Date
InFile "D://test.txt"
Append into table TT
Fields terminated by ","
Trailingnullcols
(
ID integer external,
Name "Upper (: Name)",
Con ": id| |:name",
Dtdate "Yyyy-mm-dd"
)

Test.txt data is as follows
1,a,,2007-07-8
2,b,,2008-07-8
3,c,,2009-07-8

Appendix:有效的关键字:方便今后使用和查询。

     userid -- ORACLE 用户名/口令            control -- 控制文件名                        log -- 日志文件名                            bad -- 错误文件名                          data -- 数据文件名                      discard -- 废弃文件名 discardmax -- 允许废弃的文件的数目         (全部默认)        skip -- 要跳过的逻辑记录的数目  (默认 0)        load -- 要加载的逻辑记录的数目  (全部默认)      errors -- 允许的错误的数目         (默认 50)        rows -- 常规路径绑定数组中或直接路径保存数据间的行数                 (默认: 常规路径 64, 所有直接路径)    bindsize -- 常规路径绑定数组的大小 (以字节计)  (默认 256000)      silent -- 运行过程中隐藏消息 (标题,反馈,错误,废弃,分区)      direct -- 使用直接路径                     (默认 FALSE)     parfile -- 参数文件: 包含参数说明的文件的名称    parallel -- 执行并行加载                    (默认 FALSE)        file -- 要从以下对象中分配区的文件     skip_unusable_indexes -- 不允许/允许使用无用的索引或索引分区  (默认 FALSE) skip_index_maintenance -- 没有维护索引, 将受到影响的索引标记为无用  (默认 FALSE) commit_discontinued -- 提交加载中断时已加载的行  (默认 FALSE)    readsize -- 读取缓冲区的大小               (默认 1048576) external_table -- 使用外部表进行加载; NOT_USED, GENERATE_ONLY, EXECUTE  (默认 NOT_USED) columnarrayrows -- 直接路径列数组的行数  (默认 5000) streamsize -- 直接路径流缓冲区的大小 (以字节计)  (默认 256000) multithreading -- 在直接路径中使用多线程   resumable -- 启用或禁用当前的可恢复会话  (默认 FALSE) resumable_name -- 有助于标识可恢复语句的文本字符串 resumable_timeout -- RESUMABLE 的等待时间 (以秒计)  (默认 7200) date_cache -- 日期转换高速缓存的大小 (以条目计)  (默认 1000) no_index_errors -- 出现任何索引错误时中止加载  (默认 FALSE) PLEASE NOTE: 命令行参数可以由位置或关键字指定 。前者的例子是 ‘sqlldr scott/tiger foo ‘; 后一种情况的一个示例是 ‘ sqlldr control=foo userid=scott/tiger‘。位置指定参数的时间必须早于 但不可迟于由关键字指定的参数。例如, 允许 ‘sqlldr scott/tiger control=foo logfile=log‘ , 但是 不允许 ‘sqlldr scott/tiger control=foo log‘ , 即使 参数 ‘log‘ 的位置正确。

Oracle SQLLDR Command