Oracle high-speed Batch Data Loading tool SQL * loader instructions

SQL * Loader (SQLLDR) is an Oracle high-speed Batch Data Loading tool. This is a very useful tool for loading data to the Oralce database in multiple flat file formats. SQLLDR can load a large amount of data in a very short time. It has two operation modes.
Traditional path(Conventional path): SQLLDR uses SQL insert to load data for us.
Direct path: In this mode, SQLLDR directly formats database blocks instead of SQL.
Using direct path loading, you can read data from a flat file and write it directly to a formatted database block, bypassing the entire SQL engine and undo generation, it may also avoid redo generation. To fully load data in a database without any data, the fastest way is to load data using parallel direct paths.

To use SQLLDR, you must have a control file ). The control file contains information describing the input data (such as the layout and type of the input data), as well as information about the target table. Control files can even contain the data to be loaded.

1. Create a new control file demo1.ctl with the following content:
Copy codeThe Code is as follows:
LOAD DATA
INFILE *
INTO TABLE DEPT
Fields terminated ','
(DEPTNO, DNAME, LOC)
BEGINDATA
10, Sales, Virginia
20, Accounting, Virginia
30, Consulting, Virginia
40, Finance, Virginia

Load data: This will tell SQLLDR what to do (in this example, it indicates the DATA to be loaded ). SQLLDR can also execute CONTINUE_LOAD, that is, Continue loading.
INFILE *: This tells SQLLDR that the data to be loaded is actually contained in the control file, for example, 6th ~ 10 rows. You can also specify the file name of another file that contains data.
Into table dept: This tells SQLLDR which TABLE to load the data to (in this example, the data is loaded to the dept table ).
Fields terminated by ',': This tells SQLLDR that the data format should be separated BY commas.
(DEPTNO, DNAME, LOC): This tells SQLLDR the column to be loaded, the sequence of these columns in the input data, and the data type. This refers to the data type in the input stream, not in the database. In this example, the Data Type of the column is CHAR (255) by default, which is sufficient.
BEGINDATA: This will tell SQLLDR that you have completed the description of the input data, followed by the rows (7th ~ 10 rows) is the specific data to be loaded to the DEPT table.
2. Create an empty table dept
Copy codeThe Code is as follows:
Create table dept
(Deptno number (2) constraint dept_pk primary key,
Dname varchar2 (14 ),
Loc varchar2 (13)
)
/

3. load data
Copy codeThe Code is as follows:
Sqlldr userid = lwstest/netjava control = d: \ demo1.ctl