"ORACLE" mobile data (Directory Object)

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)