Sql*loader (SQLLDR) is a high-speed batch data loading tool for Oracle. This is a useful tool for loading data into the Oralce database in a variety of flat file formats. SQLLDR can load large amounts of data in a very short period of time. It has two modes of operation.
Traditional Path: (Conventional path): Sqlldr uses SQL inserts to load data for us.
Direct path: In this mode, SQLLDR does not use SQL, but instead directly formats the database block.
With direct path loading, you can read data from a flat file and write it directly to the formatted database block, bypassing the entire SQL engine and undo generation, and possibly avoiding redo generation. The quickest way to load data fully in a database without any data is to use parallel direct path loading.
To use SQLLDR, you need a control file. The control file contains information that describes the input data (such as the layout of the input data, the data type, and so on), and also contains information about the target table. The control file can even contain data to be loaded.
1. Create a new control file Demo1.ctl, which reads as follows:
Copy Code code as follows:
LOAD DATA
INFILE *
Into TABLE DEPT
FIELDS terminated by ', '
(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 case, indicate that you want to load the data). Sqlldr can also perform continue_load, which is to continue loading.
INFILE *: This tells Sqlldr that the data to be loaded is actually contained on the control file itself, as shown in line 6th to 10th. 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 into (in this case, the data is loaded into the DEPT table).
FIELDS terminated by ', ': This tells Sqlldr that the form of the data should be a comma-delimited value.
(DEPTNO, Dname, LOC): This tells Sqlldr the columns to be loaded, the order of these columns in the input data, and the data type. This refers to the data type of the data in the input stream, not the data type in the database. In this example, the data type of the column defaults to char (255), which is sufficient.
Begindata: This will tell Sqlldr that you have completed a description of the input data, followed by the line (line 7th to 10th) is the specific data to be loaded into the Dept table.
2. Create an empty table dept
Copy Code code as follows:
CREATE TABLE Dept
(Deptno number (2) Constraint DEPT_PK primary key,
Dname VARCHAR2 (14),
Loc VARCHAR2 (13)
)
/
3. Loading data
Copy Code code as follows:
Sqlldr Userid=lwstest/netjava Control=d:\demo1.ctl
