Simple use of SQL loader

The previous summary of the use of Sql*loader, and today, and then turned out to see

Sql*loader can load data from external files into a table in Oracle DB . It has a powerful data analysis engine, so there is no limit to the format of the data in the data file.

Sql*loader Use the following files:

• input data file : Sql*loader read data from one or more files specified in the control file (or equivalent files of the operating system). From the perspective of sql*loader infile

• Control Files : A control file is a text file that is written in a language that is recognized by Sql*loader. The control file indicates where sql*loader is looking for data, how to parse and interpret the data, where to insert the data, and so on. The control file is considered an imported template.

• log File : The log file is created when Sql*loader begins execution. If the log file cannot be created, execution terminates. The log file contains a detailed description of the load operation, including any errors that occurred during the loading process.

• bad file : Bad files contain sql*loader oracle DB The rejected record. When the input format is invalid, sql*loader sql*loader after processing a data file record, The data file record is sent to the oracle DB So that it can be inserted as a row into the table. If oracle DB determines that the row is valid, the row is inserted into the table If the row is determined to be invalid, the record is rejected, and then sql*loader The record will be placed in the bad file.

• Discard Files : This file is created only if you need such a file and you specify that the discard file should be enabled. A record contained in a discard file is a record that is filtered out of the load because it does not conform to any of the record selection criteria specified by the control file

Three can not be selected in the back of

The procedure is as follows:

1), Create data file, I use here is from plsql developer the emp table emp.csv.

The contents are as follows:

 

2) , the target table is to be present and will be faulted if it remains consistent with the field definition of the data file. Here I use the as Select method to Create a table Emp3based on the EMP table, and then use the delete statement to clear the table data.

3) , establish a control file user.ctl. The control files I have here are generated using the EM tool. The contents are as follows:

    

OPTIONS (skip=1,rows=128) LOAD DATA INFILE '/u01/app/oracle/oradata/orcl/emp.csv ' APPEND into TABLE SCOTT. EMP3 fields TERMINATED by ', ' optionally enclosed by ' "' (EMPNO INTEGER EXTERNAL, ename CHAR, JOB CH     AR, MGR CHAR, HireDate DATE, SAL integer EXTERNAL, COMM integer EXTERNAL, DEPTNO integer EXTERNAL )

Note:

1.Load Data statement notifies Sql*loader to start a new data load Operation

    2. INFILE keyword Specifies the name of the data file containing the data to be loaded badfile discardfile Span style= "font-family: Arial" To specify bad data and files that discard data

3.theAPPEND keyword is one of the options that you can use to load data into a non-empty table. To load data into an empty table , use the INSERT keyword.

4. into table to insert a record

5.Fields terminated by "," define the data for each row of records with "," delimited

6.Optionally enclosed by ' "-- each field in the data is in the ' " box, such as when there is a "," delimiter in the field

7. OPTIONSatthe first line ,skip=1 skips the first line,rows=128 defines the number of rows to load limit

 

4) , execute the command:

Sqlldr system/oracle Control=user.ctl

as follows:

 

5) , query validation

 

sqlldr More usage please search by yourself online ...

Simple use of SQL loader