-- Conventional load mode
---- Load. CTL File
Load data
Infile 'C: \ 1.txt'
Into Table Dept
Fields terminated ','
(Deptno, dname, Loc)
---- The data is as follows:
10, sales, Virginia
20, accounting, Virginia
30, consulting, Virginia
40, finance, Virginia
---- Create a table
Create Table Dept
(
Deptno number (2 ),
Dname varchar2 (14), S
Loc varchar2 (13)
)
Sqlldr userid = 'sys/test as sysdba 'control = 'C: \ load. CTL 'log = 'C: \ log. Log'
-- Sqlldr receives 'exceeds maximum length', because the default string of the input file is 255. If the value exceeds, an error is returned.
Alter table dept modify dname varchar2 (1000 );
---- Load. CTL File
Load data
Infile 'C: \ 1.txt'
Into Table Dept
Fields terminated ','
(Deptno, dname, Loc)
---- The data is as follows:
10, sales, Virginia
20, accounting, Virginia
30, consulting, Virginia
40, commandid aaaaaaaaaaaaaaaaaaaaaaaa, Virginia
Sqlldr userid = 'sys/test as sysdba 'control = 'C: \ load. CTL 'log = 'C: \ log. Log'
Error: 'exceeds maximum length'
---- Modify load. CTL
Load data
Infile 'C: \ 1.txt'
Into Table Dept
Fields terminated ','
(Deptno, dname char (1000), Loc)
Sqlldr userid = 'sys/test as sysdba 'control = 'C: \ load. CTL 'log = 'C: \ log. Log'
-- How to load delimiters
Load data
Infile 'C: \ 1.txt'
Into Table Dept
Fields terminated by ', 'optionally enclosed '"'
(Deptno, dname char (1000), Loc)
---- The data is as follows:
10, sales, "Virginia, USA"
20, accounting, "Va," "Virginia """
30, consulting, Virginia
40, finance, Virginia
---- The result is as follows:
10, sales, Virginia, USA
20, accounting, VA, "Virginia"
30, consulting, Virginia
40, finance, Virginia
---- Modify load. CTL
Load data
Infile 'C: \ 1.txt'
Into Table Dept
Fields terminated by x' 07 'optionally enclosed '"'
(Deptno, dname char (1000), Loc)
Sqlldr userid = 'sys/test as sysdba 'control = 'C: \ load. CTL 'log = 'C: \ log. Log'
---- The data is as follows. Use x '07 'as the delimiter.
10 sales "Virginia, USA"
20 accounting "Va," "Virginia """
30 consultingvirginia
40 financevirginia
---- The result is as follows:
10 salesvirginia, USA
20 accountingva, "Virginia"
30 consultingvirginia
40 financevirginia
-- Load date
Alter table dept add last_update date;
---- Modify load. CTL
Load data
Infile 'C: \ 1.txt'
Into Table Dept
Fields terminated by x' 07 'optionally enclosed '"'
(Deptno, dname char (1000), Loc,
Last_update date "yyyy-mm-dd ")
Sqlldr userid = 'sys/test as sysdba 'control = 'C: \ load. CTL 'log = 'C: \ log. Log'
---- The data is as follows:
10 sales "Virginia, USA" "2008-01-01"
20 accounting "Va," "Virginia" 2008-01-01"
30 consultingvirginia "2008-01-01"
40 financevirginia "2008-01-01"
-- Use functions
Alter table dept add entire_line varchar2 (200 );
---- Modify load. CTL
Load data
Infile 'C: \ 1.txt'
Into Table Dept
Fields terminated by x' 07 'optionally enclosed '"'
Trailing nullcols
(Deptno,
Dname char (1000) "upper (: dname )",
Loc "upper (: Loc )",
Last_update date "yyyy-mm-dd ",
Entire_line ": deptno |: dname |: last_update ")
Sqlldr userid = 'sys/test as sysdba 'control = 'C: \ load. CTL 'log = 'C: \ log. Log'
---- The data is as follows:
10 sales "Virginia, USA" "2008-01-01"
20 accounting "Va," "Virginia" 2008-01-01"
30 consultingvirginia "2008-01-01"
40 financevirginia "2008-01-01"
-- How to load embedded line breaks
---- Modify load. CTL
Load data
Infile 'C: \ 1.txt'
Into Table Dept
Fields terminated by x' 07 'optionally enclosed '"'
Trailing nullcols
(Deptno,
Dname char (1000) "upper (: dname )",
Loc "upper (: Loc )",
Last_update date "yyyy-mm-dd ",
Entire_line "Replace (: entire_line, '\ n', 'chr (10 )')")
Sqlldr userid = 'sys/test as sysdba 'control = 'C: \ load. CTL 'log = 'C: \ log. Log'
---- The data is as follows:
10 sales "Virginia, USA" "2008-01-01" this is \ n aaa
20 accounting "Va," "Virginia" 2008-01-01 "this is \ n aaa
30 consultingvirginia "2008-01-01" this is \ n aaa
40 financevirginia "2008-01-01" this is \ n aaa
Select utl_raw.cast_to_raw (CHR (6) | CHR (13) | CHR (10) from dual
------------
'060d0a'
---- Modify load. CTL
Load data
Infile 'C: \ 1.txt '"str x '060d0a '"
Into Table Dept
Fields terminated by x' 07 'optionally enclosed '"'
Trailing nullcols
(Deptno,
Dname char (1000) "upper (: dname )",
Loc "upper (: Loc )",
Last_update date "yyyy-mm-dd ",
Entire_line)
Sqlldr userid = 'sys/test as sysdba 'control = 'C: \ load. CTL 'log = 'C: \ log. Log'
---- The data is as follows:
10 sales "Virginia, USA" "2008-01-01" this is AAA
20 accounting "Va," "Virginia" 2008-01-01 "this is AAA
30 consultingvirginia "2008-01-01" this is AAA
40 financevirginia "2008-01-01" this is AAA
Original article, if reproduced, please mark the author: Tian Wen csdn address: http://blog.csdn.net/tiwen818