sql> Create or replace directory Dir_dt as '/home/oracle ';
Directory created.
Sql> Grant Read,write on directory Dir_dt to Scott;
Grant succeeded.
Sql*loader
Test One: infile *
Common parameters
Parameter name |
Meaning |
Userid |
ORACLE Username/password |
Control |
Control files |
Log |
Log file for logging |
Bad |
Bad data file |
Data |
Data files |
Discard |
Discarded data files |
Discardmax |
Maximum value allowed to discard data (all default) |
Skip |
Number of logical records to skip (default 0) |
Load |
Number of logical records to load (all default) |
Errors |
Number of error records allowed (default 50) |
Rows |
Number of rows in conventional path array or between direct path data saves (number of records per commit, default: Normal path 64, all direct paths) |
Bindsize |
Size of conventional path bind array in bytes (default 256000) The size of the buffer for each commit record (in bytes, default 256000) |
Silent |
Suppress output information (header,feedback,errors,discards,partitions) |
Direct |
Import using Passthrough path mode (default false) |
Parfile |
Parameter file:name of file that contains parameter specificatiions |
Parallel |
Parallel import (default false) |
Control File Basic Format
Load data
InFile ' T_01.dat '--the name of the data file to import
--infile ' t_02.dat '--if you have multiple data files, you can write more than one
--infile *-The content to be imported is in the control file, and the Begindata is followed by the imported content (cannot be used in conjunction with the above format)
Insert: How data is loaded (default)
There are four types of loading methods:
Append: The original table has data to add to the back
Insert: (default) load empty table, if the original table has data Sqlloader will stop
Replace: Original table with data the original data will be deleted all
Truncate: The same content as replace will delete the existing data with the TRUNCATE statement
Badfile ' Bf_name.bad ': Specifies the location and name of the record where the error occurred. If this parameter is not specified, a file with the same name as the file holding the data will be generated by default in the same directory as the control file.
Fields terminated by ', ' optionally enclosed by ' '
The data is reproduced in the format, with ', ' delimited data, and with ' ' to identify the beginning of a field. The main reason is that, in a flat text file, it is possible to have a comma-like field, in which case the Sqlloader will mistakenly assume that the comma is a delimiter, causing the load data to be incorrect. Note: This parameter can be declared or not declared, and if not declared, you need to declare what to differentiate in the field where you define it.
Trailing nullcols: null is allowed, in the case of a flat text file, there is no value for the field in the corresponding table, then NULL is substituted. If you do not add this parameter, the record that corresponds to it cannot be written to the table and appears in the bad file.
(Col_name1,col_name2,col_name3): Declares the name of all fields.
If the field terminated by ', ' is not declared, then it can also be declared in the fields as follows:
(col_name1 [Interger external] terminated by ', ',
col_name2 [Date "dd-mon-yyyy] terminated by ', ',
Col_name3 [char] terminated by ', ' optionall enclosed by ')
If you do not declare the fields terminated by ', ' and the text file does not have any delimited identifiers, you can also load the data in the same way as the specified location, as follows:
(col_name1 position (1:2),
Col_name2 position (3:9),
Col_name3 (*:15) char (8),//char (8) Specifies the field type and length, *:15, representing, starting at the end of the previous field, 15 ends
Col_name4 position (16:30) "Trim (: Col_name4)"//remove spaces on both sides of the characters intercepted by this field
)
Begindata: Echo with infile*, that is, the data to be imported is in the control file, and below the Begindata
--------------------------------------------------------------------------------------------------------------- ----------------
Sql*loader:release 11.2.0.4.0-production on Mon may 14:47:19 2017sql> Conn Scott/tiger
connected.
Sql> CREATE TABLE Sl_base
2 (ID number (5), fname VARCHAR2 (Ten), lname VARCHAR2);
Table created.
Sql> exit
disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production
With The partitioning, OLAP, Data Mining and Real Application testing Options
[[email protected] adump]$ CD
[email protected] ~]$ VI base.ctl
Load Data
infile *
badfile ' Base.bad '
into table sl_base
Replace
Fields terminated by ', '
(id,fname,lname)
Begindata
1,zhangfei,zhangyide
2,guanyu,guanyunchang
3, Liubei,liuxuande
~
~
~
"Base.ctl" [New] 12L, 182C written  
[[email protected] ~]$ sqlldr scott/tiger control = base.ctl
Copyright (c) 1982, Oracle and/or its affiliates. All rights reserved.
Commit Point Reached-logical Record Count 3
[email protected] ~]$ cat Base.bad
2,guanyu,guanyunchang
Sql> Conn Scott/tiger
Connected.
Sql> select * from Sl_base;
ID FNAME LNAME
---------- -------------- --------------
1 Zhangfei zhangyide
3 Liubei Liuxuande
Test two: null value
[[email protected] ~]$ ls
afiedt.buf Base.bad base.ctl base.log c.sql datadump Rman
[email protected] ~]$ RM base*
[[email protected] ~]$ ls
afiedt.buf c.sql datadump Rman
[email protected] ~]$ VI base_data.dat
1,zhangfei,zhangyide
2,guanyu,guanyunchang
3,liubei,liuxuande
4,kongming
~
~
"Base_data.dat" [New] 4L, 73C written
[email protected] ~]$
[email protected] ~]$ VI base.ctl
Load data
InFile ' Base_data.dat '
into table Sl_base
Truncate
Fields terminated by ', '
(Id,fname,lname)
~
~
"Base.ctl" [New] 7L, 104C written
[Email protected] ~]$ Sqlldr Scott/tiger control=base.ctl
Sql*loader:release 11.2.0.4.0-production on Mon 29 16:32:08 2017
Copyright (c) 1982, Oracle and/or its affiliates. All rights reserved.
Commit Point reached-logical Record Count 4
[email protected] ~]$ ll
Total
-rw-r--r--1 Oracle oinstall 20:09 afiedt.buf
-rw-r--r--1 Oracle Oinstall 104 may 16:26 Base.ctl
-rw-r--r--1 Oracle oinstall 16:32 Base_data.bad
-rw-r--r--1 Oracle oinstall 16:24 base_data.dat
-rw-r--r--1 Oracle oinstall 1839 may 16:32 Base.log
-rw-r--r--1 Oracle oinstall 295 may 16:31 Control-base.log
-rw-r--r--1 Oracle oinstall 11:50 c.sql
drwxr-xr-x 2 Oracle oinstall 4096 may 21:50 datadump
drwxr-xr-x 2 Oracle oinstall 4096 may 11:57 Rman
[email protected] ~]$ cat Base_data.bad
4,kongming
2,guanyu,guanyunchang
Bad file has two records, the record with ID 2 is not imported because the LName field is not long enough and the record with ID 4 is not imported because the LName field has a null value
Sql> select * from Sl_base;
ID FNAME LNAME
---------- ---------- ----------
1 Zhangfei zhangyide
3 Liubei Liuxuande
Resolving null value issues, editing control files
[Email protected] ~]$ VI base.ctl
Load data
InFile ' Base_data.dat '
into table Sl_base
Truncate
Fields terminated by ', '
Trailing Nullcols
(Id,fname,lname)
~
~
[Email protected] ~]$ Sqlldr Scott/tiger control=base.ctl
Sql*loader:release 11.2.0.4.0-production on Mon 29 16:39:37 2017
Copyright (c) 1982, Oracle and/or its affiliates. All rights reserved.
Commit Point reached-logical Record Count 4
[email protected] ~]$ cat Base_data.bad
2,guanyu,guanyunchang
At this point, the record of control errors is no longer available.
Test three: The string contains a comma
[[email protected] ~]$ rm base*
[[ email protected] ~]$ ls
afiedt.buf control-base.log c.sql datadump Rman
[[email protected] ~]$ vi base.ctl
load data
infile ' Base_data.dat '
into table Sl_ Base
truncate
trailling Nullcols
(ID terminated by ', ',
FName terminated by ', ',
lname terminated by ', ' optionally enclosed by ' "'
)
~
~
[[email protected] ~]$ rm base*
[[ email protected] ~]$ ls
afiedt.buf control-base.log c.sql datadump Rman
[[email protected] ~]$ vi base.ctl
Load data
infile ' Base_data.dat '
into table Sl_base
truncate
trailing nullcols
(ID terminated by ', ',
lname terminated by ', ' optionally enclosed by ' '
Sql*loader:release 11.2.0.4.0-production on Mon 29 17:02:22 2017
Copyright (c) 1982, Oracle and/or its affiliates. All rights reserved.
Commit Point Reached-logical Record Count 6
[email protected] ~]$ cat Base_data.bad
2,guanyu, "Guan,yunchang"
Sql> select * from Sl_base;
ID FNAME LNAME
---------- ---------- ----------
1 Zhangfei zhang,yide
3 Liubei Liu,xuande
4 kongming
Test four: No separators in data files
[Email protected] ~]$ RM base*
[Email protected] ~]$ VI Base_data.dat
1zhangfeizhangyide
2guanyu Guanyunchang
3liubei Liuxuande
4kongming
~
~
[Email protected] ~]$ VI base.ctl
Load data
InFile ' Base_data.dat '
into table Sl_base
Truncate
Trailing Nullcols
(ID position (1:1),
FName position (2:9),
LName Position (10:22)
)
~
~
[Email protected] ~]$ Sqlldr Scott/tiger control=base.ctl
Sql*loader:release 11.2.0.4.0-production on Mon 29 17:12:25 2017
Copyright (c) 1982, Oracle and/or its affiliates. All rights reserved.
Commit Point Reached-logical Record Count 5
Sql> select * from Sl_base;
ID FNAME LNAME
---------- ---------- ----------
1 Zhangfei zhangyide
3 Liubei L Iuxuande
4 kongming
"ORACLE" mobile data (Directory Object)