SQL Loader Basic Application

A.ctl

Load data
infile *---Specify the load file, * indicates that the data is behind the control file
into table Bonus---the specified name
Field terminated by ","---The specified area delimiter is a comma
(ename,job,sal)---The column name of the specified table
Begindata---Valid only if infile specified *
smith,cleak,3904
allen,salesman,2891
ward,salesman,3128
........


The following command can be used to import
Sqlldr Userid/pass Control=a.ctl


one, the file to be loaded is not separated by commas
A, modify the original data, replace the delimiter with a comma
b, modify the control file to change the value of the fields terminated by to the actual delimiter


two. What to do if the data to be loaded contains delimiters, such as the following table Kkk.dat
smith,cleak,3904
Allen, "salesman," AK ", 2891
Ward, "Salesman,m", 3128
You need to change the control file at this time
Load data
InFile Kkk.dat---Specify the load file, * indicates that the data is in the control Behind files
into table Bonus---the specified name
Field terminated by "," optinonally enclosed by ' "'---The specified area delimiter is a comma
(ename,job,sal)---The column name of the specified table

optinonally enclosed by ' "' is the default is double quotation marks, if the other, the double quotation marks can be changed



third, the data file does not have the delimiter what to do, is the fixed length string Kkk,dat
Smith Cleak 3904
Allen salesman 2891
Ward salesman 3128
modifying control files
Load data
InFile Kkk.dat
TRUNCATE TABLE Bonus
(
Ename position (1:5), Position (1:5) refers to the value from the first word Fu Cai to the fifth character as the ename, the absolute offset
Job position (7:15),
Sal Position (17:20)
)

Position (*+2:15), relative offset, which represents the offset from the last position after two start-up characters, which are loaded to the actual 15th character
Position (*) char (9) The advantage of relative offset + type and length is that you only need to specify the starting position for the first column, and the other only needs to specify the column length.


Four, the columns than in the data file have fewer columns for the table to import, and the empty column must be assigned a value
such as bonus in a column comm, and assign the initial value of 0, you can write
Load data
InFile Kkk.dat
TRUNCATE TABLE Bonus
(
Ename position (1:5),
Job position (7:15),
Sal position (17:20),
Comm ' 0 '
)
If you want to enter a special value, you can use the function to solve
Load data
InFile Kkk.dat
TRUNCATE TABLE Bonus
(
Ename position (1:5),
Job position (7:15),
Sal position (17:20),
Comm "substr (: sal,1,1)" Takes the first column of the SAL value and assigns a value to the Comm column
)
Of course, you can also write custom functions with PL/SQL to assign values



Five. What to do if the columns than in the data file are more than the columns in the table to import
A, remove multiple columns from the data file
b, filter, or do not record this data in the control file
Load data
InFile Kkk.dat
TRUNCATE TABLE Bonus
(
Ename position (1:5),
Job position (7:15),
Sal position (17:20),
Tcol filler position (22:30),--tcol If this column is not entered, it is filtered out, or the line does not appear in the control file
)
This is true if the data file appears as a delimiter in this case
Load data
InFile Kkk.dat
TRUNCATE TABLE Bonus
Fields terminated by ","
(ename, Job, Sal, Tcol filler)


Six, multiple data files in the same table, the condition is that these data files in the same format
Load data
InFile Kkk.dat
InFile Kkk2.dat
InFile Kkk3.dat
TRUNCATE TABLE Bonus
Fields terminated by ","
(ename, Job, Sal)


Seven, the same data file to import a different table
Bon Smith cleak 3904
Bon Allen Saler 2891
Mgr King Tech 2543
Mgr SMM ADMD 3032
to bring the data into the B and M tables.
Load data
InFile Kkk.dat
Discardfile LDR_CASE9.DSC
Truncate
into table B
When tab= ' bon ' if there is more than one keyword, you can use and, you cannot use or
(Tab filler position (1:3),
Ename position (5:9),
Job position (*+1:18),
Sal Position (*+1)
)
into table M
When tab= ' Mgr '
(Tab filler position (1:3),
Ename position (5:9),
Job position (*+1:18),
Sal Position (*+1)
)



Eight, data file first n rows not imported
Sqlddr Scott/scott control=ldr_case1.ctl skip=3 meaning the first three lines do not import, starting from the fourth line of work
Sqlddr Scott/scott control=ldr_case1.ctl skip=3 load=6 first three lines do not import, import the next 6 rows


Nine, there are line breaks in the data to be loaded
Manually specified line breaks, the data file's newline character is not a standard line break flag, but rather a user-defined identifier or multiple characters,
Ten, Smith,sales Amnager,this is Amith,\nhe is a sales manager.
One, Allen.w,tech Manager,this is Allen.w.\nhe is a tech manager.
Blake,hr Manager,this is Blake.\nhe is a HR manager.
The wording of the control file
Load data
InFile Ldr_case11_1.dat
Truncate into Table Manager
Fields terminated by ","
(Mgrno,mname,job,
Remark "replace (: Remark, ' \\n ', Chr (10))"
)
If the data file is a fixed-length character.
Smith Sales Manager This is Smith
He is a sales manager
ALLENW Tech Manager This is Allen W
He is a tech manager.
Blake HR Manager This is Blake
He is a HR manager.
Load data infile ldr_case11_2.dat "fix 68" is the length of each line that is specified by the Fix Value property before the file is loaded, where 68 characters per line include a newline character,
Line breaks at the specified length, regardless of whether there is a newline character in the middle, so it can only be used for fixed-length string data files.
Because only the string length is fixed, you know what value should be specified at infile.
Truncate into Table Manager
(
Ename position (1:8),
Job position (10:16),
Zhiwei position (*+1:22),
Remark position (*+1:65)
)
The line break in Windows is actually composed of two characters, carriage return to add the line Chr (+CHR), Linux/unix only one character Chr (10) can be
Char_string: Normal character, which is the standard visible character,
\ n, which represents a newline, \ t represents a row tab, \f represents a page break \v represents a Column tab \ r indicates a carriage return
Use \ r \ n Linux/unix under Windows.
Example of line end wrap:
Data files
10,smith,sales Manager,this is Smith.
He is a sales manager. |
11,allen.w,tech Manager,this is ALLEN.W.
He is a tech manager. |
Control files
Load data
infile ldr_case1_4.dat "str" | \ r \ n "
Truncate into Table Manager
Fields terminated by ', '
(Mgrno,maname,job,remarek)


10, to import large segments (LOB type)
1, the data is saved in the data file
Load data
infile ldr_case12_1.dat "str ' |\r\n '"
Truncate into Table Manager
Fields terminated by "," optionally enclosed by ' "',
(Mgrno,mname,job,remark char (10000))
Assuming that the remark column has a large amount of text, you can define
2, data files are saved in a separate file
Sql> CREATE TABLE Lobtbl (
2 Fileowner varchar2 (30),
3 filename varchar2 (200),
4 filesize number,
5 Filedata Clob,
6 create_date date);
The data files are as follows Ldr_case12_2.dat
2009-03-17 09:43 154 Junsansi F:\oracle\script\ldr_case11_1.ctl
2009_03_17 09:44 189 Junsansi F:\oracle\script\ldr_case11_1.dat
2009_03_17 09:45 2,639 Junsansi f:\oracle\script\ldr_case11_4.log
The control files are as follows
Load Date
InFile Ldr__case12_2.dat
Truncate into table Lobtbl
(Create_date position (1:17) Date ' Yyyy-mm-dd Hh24:mi ',
FileSize position (*+1:25) "To_number (: FileSize, ' 99,999,999 ')",
Fileowner position (*+1:34),
FileName position (*+1) char ($) "substr (: Filename,instr (: FILENAME, ' \ \ ',-1) +1)",
Filedata lobfile (filename) terminated by EOF)


11, some fields have null values:
Load data
InFile Ldr_case13.dat
Truncate into table bonus
Fields terminated by "," trailing nullcols
(ename,job,sal)

12, import of large amounts of data
Sqlldr Scott/scott control=ldr_object.ctl errors=10 rows=640
Indicates that 10 rows are wrong and jumps out, loading 640 rows at a time. Default is 64 rows
If the 640 line is too large, the log information is prompted, this time to modify the Bindsize parameter, the default is 256k, we modify it to 10m (
1024x1024x10=10485760) This time increase the line to 5000 rows.
This will be faster,
Sqlldr scott/[email protected]_192.168.1.250 control=ldr_object.ctl errors=10 rows=5000 bindsize=10485760
But it can be faster.
Sqlldr Scott/scott Control=ldr_object.ctl direct=true;
But it can be faster.
Streamsize: Direct path loading reads all records by default, so there is no need to set the rows parameter, and the read data is stored in the buffer, i.e.
The streamsize parameter, which is 256k, is increased here to 10MB
Date_cache: This parameter specifies a converted date format buffer, in bars, the default value of 1000, that is, save 1000 converted date format,
Since the data we are importing has a date format, this parameter is increased to 5000 to reduce the overhead associated with the date conversion operation.
Sqlldr scott/tiger control=ldr_object.ctl direct=true streamsize=10485760 date_cache=5000

SQL Loader Basic Application