Oracle SQL loader application

I. Features of SQL Loader
Oracle has many tools for data migration, backup, and recovery. However, each tool has its own characteristics.
For example, exp and IMP can be used to export and export data in the database, which is a good tool for database backup and recovery. Therefore, exp and IMP are mainly used for hot backup and recovery of the database. It has the advantages of high speed, easy to use, and fast. At the same time, it also has some disadvantages. For example, during the export and import process between databases of different versions, there will always be one or another problem, this may be the compatibility of Oracle's own products.
The SQL loader tool does not solve this problem. It can smoothly import data stored in text format to the Oracle database, it is a convenient and common tool for data migration between different databases. The disadvantage is that the speed is relatively slow, and it is a little troublesome for blob and other types of data.

Ii. SQL loader help

C:/> sqlldr
 

Iii. SQL loader example
A) sqlloader exports Excel Data to Oracle
1. Create the files required for SQL * loader input data and save them to C:/. Edit them in Notepad:
Control File: Input. CTL. The content is as follows:

Load data -- 1. Control File ID
Infile 'test.txt '-the name of the data file to be input is test.txt
Append into table test -- 3. append records to Table Test
Fields terminated by X '09' -- 4. The field is terminated on X '09' and is a tab)
(ID, username, password, SJ) ----- defines the column Sequence

A. insert, which is the default mode. The table is required to be empty when data loading starts.
B. append: Add a new record to the table
C. Replace: delete old records and replace them with newly loaded records.
D. truncate, same as above

Use the SQL * loader command in the DOS window to input data.

C:/> sqlldr userid = system/Manager Control = input. CTL
The default log file name is input. log.
The default bad record file is input. Bad.

2. Another Method
You can save an Excel file as CSV (separated by commas) (*. CSV). The control file is separated by commas (,).
Load data
Infile 'd:/car.csv'
Append into Table t_car_temp
Fields terminated ","
(Phoneno, vip_car)

B) Directly import data into the control file

1. Control File test. CTL content
-- The format for executing this file with SQL loader is:
-- Sqlldr control = <FILENAME> be sure to substitute your
-- Version of SQL loader and the filename for this file.
Load data
Infile *
Badfile 'C:/Documents and Settings/jackey/desktop/wmcountry. bad'
Discardfile 'C:/Documents and Settings/jackey/desktop/wmcountry. DSC'
Insert into Table emccountry
Fields terminated by ";" optionally enclosed '"'
(
CountryID nullif (countryID = "null "),
Countrycode,
Countryname,
Continentid nullif (continentid = "null "),
Mapid nullif (mapid = "null "),
Createtime date "mm/DD/YYYY hh24: MI: SS" nullif (createtime = "null "),
Lastmodifiedtime date "mm/DD/YYYY hh24: MI: SS" nullif (lastmodifiedtime = "null ")
)
Begindata
1; "JP"; "Japan"; 1; 9; "16:31:32"; null
2; "cn"; "China"; 1; 10; "16:31:32"; null
3; "in"; "India"; 1; 11; "16:31:32"; null
4; "au"; "Australia"; 6; 12; "16:31:32"; null
5; "ca"; "Canada"; 4; 13; "16:31:32"; null
6; "us"; "United States"; 4; 14; "16:31:32, 09/16/2004"; null
7; "MX"; "Mexico"; 4; 15; "16:31:32"; null
8; "GB"; "United Kingdom"; 3; 16; "16:31:32"; null
9; "de"; "Germany"; 3; 17; "16:31:32"; null
10; "FR"; "France"; 3; 18; "16:31:32"; null