Oracle Tutorial: using SQL * Loader for high-speed Batch Data Loading

1. The control file contains the data to be loaded.

First, create a test table.

1. SQL> showUser
2. USERIs"ING"
3. SQL>Create TableDept
4. 2 (deptno number (10)ConstraintDept_pkPrimary Key,
5. 3 dname varchar2 (20 ),
6. 4 loc varchar2 (20 ));
8. The table has been created.

Create a control file (including the loaded data)

1. [Oracle @ linux sqlldr] $ pwd
2. /U01/sqlldr
3. [Oracle @ linux sqlldr] $ cat demo1.ctl
4. LOADDATA
5. INFILE *
6. INTO TABLEDEPT
7. FIELDS TERMINATEDBY ','
8. (DEPTNO, DNAME, LOC)
9. BEGINDATA
10. 10, Sales, Virginia
11. 20, Accounting, Virginia
12. 30, Consulting, Virginia
13. 40, Finance, Virginia

Then execute the load command in the command line.

1. [Oracle @ linux sqlldr] $ pwd
2. /U01/sqlldr
3. [Oracle @ linux sqlldr] $ sqlldr userid = ing/ing control = demo1.ctl log = demo1.log
5. SQL * Loader: Release 10.2.0.4.0-ProductionOnTuesday October 4 18:39:54 2011
7. Copyright (c) 1982,200 7, Oracle.AllRights reserved.
9. Submission point reached-logic record count 4

View dept table

1. SQL>Select*FromDept;
3. DEPTNO DNAME LOC
4. --------------------------------------------------
5. 10 Sales Virginia
6. 20 Accounting Virginia
7. 30 Consulting Virginia
8. 40 Finance Virginia

View the demo1.log Log File

1. [Oracle @ linux sqlldr] $ pwd
2. /U01/sqlldr
3. [Oracle @ linux sqlldr] $ cat demo1.log
5. SQL * Loader: Release 10.2.0.4.0-ProductionOnTuesday October 4 18:39:54 2011
7. Copyright (c) 1982,200 7, Oracle.AllRights reserved.
9. Control File: demo1.ctl
10. Data File: demo1.ctl
11. Error file: demo1.bad
12. Obsolete file: Not specified
14. (All records can be discarded)
16. Number of objects to be loaded:ALL
17. Number of to be skipped: 0
18. Allowed error: 50
19. Bound array: 64 rows, up to 256000 bytes
20. Continue: Unspecified
21. Path used: General
23. Table DEPT, loaded from each logical record
24. Insert option for this tableINSERTEffective
26. Column name location length abort packaging Data Type
27. --------------------------------------------------------------------------
28. DEPTNOFIRST*,CHARACTER
29. DNAMENEXT*,CHARACTER
30. LOCNEXT*,CHARACTER
33. Table DEPT:
34. Four rows are loaded successfully.
35. Zero rows are not loaded due to data errors.
36. Since allWHENClause failed. 0 rows are not loaded.
37. Because all fields are empty, 0 rows are not loaded.
40. Space allocated to the bound array: 49536 bytes (64 rows)
41. Number of bytes read from the buffer: 1048576
43. Total number of skipped logical records: 0
44. Total number of logical records read: 4
45. Total number of rejected logical records: 0
46. Total number of discarded logical records: 0
48. Starting from 18:39:54, January 1, 2011, Tuesday
49. The operation ended at 18:39:54, January 1, October 04, 2011.
51. Elapsed time: 00: 00: 00.68
52. CPU time: 00: 00: 00.07

2. Separate control files from data files

Continue to use the previous dept table. First, create a control file and a data file.

1. [Oracle @ linux sqlldr] $ pwd
2. /U01/sqlldr
3. [Oracle @ linux sqlldr] $ cat demo2.ctl
4. LoadData
5. Infile demo2.data
6. AppendInto TableDept
7. Fields terminatedBy ','
8. (Deptno, dname, loc)
9. [Oracle @ linux sqlldr] $ cat demo2.data
10. 50, Sales, Virginia
11. 60, Accounting, Virginia
12. 70, Consulting, Virginia
13. 80, Finance, Virginia

Then execute the load command in the command line.

1. [Oracle @ linux sqlldr] $ sqlldr userid = ing/ing control = demo2.ctl
3. SQL * Loader: Release 10.2.0.4.0-ProductionOnTuesday October 4 18:47:23 2011
5. Copyright (c) 1982,200 7, Oracle.AllRights reserved.
7. Submission point reached-logic record count 4

Finally, view the dept table.

1. SQL>Select*FromDept;
3. DEPTNO DNAME LOC
4. --------------------------------------------------
5. 10 Sales Virginia
6. 20 Accounting Virginia
7. 30 Consulting Virginia
8. 40 Finance Virginia
9. 50 Sales Virginia
10. 60 Accounting Virginia
11. 70 Consulting Virginia
12. 80 Finance Virginia
14. Eight rows have been selected.

3. Brief description of SQL * loader

Note: The number of braces on the left side of the code below is not part of the control file, just to facilitate display.

(1) LOAD DATA
(2) INFILE *
(3) INTO TABLE DEPT
(4) fields terminated ','
(5) (DEPTNO, DNAME, LOC)
(6) BEGINDATA
(7) 10, Sales, Virginia
(8) 20, Accounting, Virginia
(9) 30, Consulting, Virginia
(10) 40, Finance, Virginia
(1): This will tell sqlldr what to do. The above example indicates loading data.
(2): * indicates to load all the data (Example 1). You can also specify the name of the data file (Example 2 ).
(3): This will tell the table to which sqlldr is to be loaded. Complete Syntax: [insert | append | replace | truncate] into table dept insert-the default value is insert. The following dept table must be empty; append-append, the following dept table can not be blank; replace-is the first to delete and then insert; truncate-is the first to truncate and then insert.
(4): Tell sqlldr to separate values with commas.
(5) indicates that sqlldr data is to be loaded into the corresponding column.
(6): It indicates that sqlldr is about to load data.
(7 )~ (10): the specific data to be loaded.

1 2 3 Next Page