[Oracle] SQL * Loader detailed tutorial (3)-Control File

[Oracle] SQL * Loader detailed tutorial (3)-control file is the most important file in SQL * Loader, it defines the location of the data file, the data format, and the behavior of configuring the data loading process. This section describes the configuration parameters of the control file. To configure the command line parameters (OPTIONS) in the control file, you can use OPTIONS to configure command line parameters in the control file, as shown below:

OPTIONS (BINDSIZE=100000, SILENT=(ERRORS, FEEDBACK) )

 

You can use INFILE to configure one or more data files. If multiple data files are configured, you can configure bad files and discarded files for each data file, as follows:

INFILE  mydat1.dat  BADFILE  mydat1.bad  DISCARDFILE mydat1.dis INFILE  mydat2.dat INFILE  mydat3.dat  DISCARDFILE  mydat3.dis INFILE  mydat4.dat  DISCARDMAX  10 0

 

If the data is contained in the control file, it can be represented by *, for example, INFILE * And BEGINDATA to identify which data is: badindata bad configuration file (BADFILE) in the following three cases, records are imported into bad files: 1. oracle error occurred during INSERT. 2. incorrect Data Loading Format 3. DISCARDFILE discarded records are records that do not conform to the WHEN definition. You can also use DISCARDMAX to configure the maximum number of records that can be discarded. If this number is exceeded, by default, the target TABLE must be empty (that is, the INSERT method ), otherwise, an error is reported. If the target table is not empty, you must specify one of the following three methods: 1. APPEND: add data to the end of the source data. REPLACE: before adding data, run delete from table to clear the TABLE. Note: REPLACE here is a TABLE-level replacement, not a row-Level 3. TRUNCATE: before adding data, run TRUNCATE table_namereuse STORAGE. If you want to insert records INTO multiple tables, you can use multiple INTO tables, such:

1 50   Manufacturing       — DEPT record 2 1119 Smith      50       — EMP record 2 1120 Snyder     50 1 60   Shipping 2 1121 Stevens    60 

 

We can insert two different tables based on the values in the first column:

INTO TABLE dept    WHEN recid = 1    (recid  FILLER POSITION(1:1)  INTEGER EXTERNAL,    deptno POSITION(3:4)  INTEGER EXTERNAL,     dname  POSITION(8:21) CHAR) INTO TABLE emp    WHEN recid <> 1    (recid  FILLER POSITION(1:1)   INTEGER EXTERNAL,    empno  POSITION(3:6)   INTEGER EXTERNAL,     ename  POSITION(8:17)  CHAR,     deptno POSITION(19:20) INTEGER EXTERNAL) 

 

WHEN a Filter Record (WHEN) uses the WHEN clause to filter the required records, any records that do not meet the WHEN condition will be discarded, such:

WHEN (deptno = '10') AND (job = 'SALES') TRAILING NULLCOLS

 

Tells SQL * Loader that if the record column is less than the column in the target table, NULL is used instead, for example:

INTO TABLE dept     TRAILING NULLCOLS ( deptno CHAR TERMINATED BY " ",   dname  CHAR TERMINATED BY WHITESPACE,   loc    CHAR TERMINATED BY WHITESPACE ) 

 

Record format: 10 Accounting for the above record, the value of the loc column is NULL. Data Field Configuration field attributes include location, data type, condition, separator, and so on. The offset (POSITION) of a data field indicates the start and end of a character, for example:

ename  POSITION (1:20)  CHAR empno  POSITION (22-26) INTEGER EXTERNAL allow  POSITION (*+2)   INTEGER EXTERNAL TERMINATED BY "/" 

 

The above * is the end of the value + offset 1, that is, from 27. Parsing a field (FILLER) does not actually import the field into the table, but serves as a condition for logical judgment. The default data type is CHAR, INTERGER indicates binary data, and interger external indicates INTERGET (n), SMALLINT, FLOAT, DOUBLE, BYTEINT, ZONED, DECIMAL, VARCHAR, CHAR, datetime and Interval data delimiters have two types: 1. TERMINATED BY2. enclosed:

Terminated by ', 'a data string, enclosed by' "'" a data string "terminated by', 'enabled BY '"' "a data string ", enclosed by '(' AND ')' (a data string) field condition (WHEN, NULLIF, DEFAULTIF)

 

The following is an example of a control file:

[plain] OPTIONS (DIRECT=true,SKIP_INDEX_MAINTENANCE=true,PARALLEL=true)  LOAD DATA  INFILE 'nor.dat'  BADFILE 'nor.bad'  DISCARDFILE 'nor.dsc'  INTO TABLE p95169.DISEASE_EXPERT_RELATION  APPEND  WHEN len='3'  FIELDS TERMINATED BY WHITESPACE  (    len FILLER POSITION(1) CHAR,    DISEASEEXPERTUUID EXPRESSION "SYS_GUID()",    EXPERTUUID CHAR,    DISEASEUUID CHAR,    DISEASESORTCODE EXPRESSION "NULL",    DISEASENAME CHAR,    CREATEDTIME EXPRESSION "TO_CHAR(sysdate,'yyyymmddhh24miss')",    MODIFIEDTIME EXPRESSION "TO_CHAR(sysdate,'yyyymmddhh24miss')"  )  INTO TABLE p95169.DISEASE_EXPERT_RELATION  APPEND  WHEN len='2'  FIELDS TERMINATED BY WHITESPACE  (    len FILLER POSITION(1) CHAR,    DISEASEEXPERTUUID EXPRESSION "SYS_GUID()",    EXPERTUUID CHAR,    DISEASEUUID EXPRESSION "NULL",    DISEASESORTCODE EXPRESSION "NULL",    DISEASENAME CHAR,    CREATEDTIME EXPRESSION "TO_CHAR(sysdate,'yyyymmddhh24miss')",    MODIFIEDTIME EXPRESSION "TO_CHAR(sysdate,'yyyymmddhh24miss')"  )  INTO TABLE p95169.DISEASE_EXPERT_RELATION  APPEND  WHEN len='1'  FIELDS TERMINATED BY WHITESPACE  (    len FILLER POSITION(1) CHAR,    DISEASEEXPERTUUID EXPRESSION "SYS_GUID()",    EXPERTUUID CHAR,    DISEASEUUID EXPRESSION "NULL",    DISEASESORTCODE EXPRESSION "NULL",    DISEASENAME EXPRESSION "NULL",    CREATEDTIME EXPRESSION "TO_CHAR(sysdate,'yyyymmddhh24miss')",    MODIFIEDTIME EXPRESSION "TO_CHAR(sysdate,'yyyymmddhh24miss')"  )