Oracle Database Load details

-- General load mode ---- load. ctl file loaddatainfile

-- General load mode ---- load. ctl File load datainfile

-- 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

,