[Oracle] SQL * Loader detailed tutorial (4)-Field List

[Oracle] SQL * Loader detailed tutorial (4)-field list [Oracle] SQL * Loader detailed tutorial (3)-Control File http://www.bkjia.com/database/201308/235328.html ... 1 (hiredate SYSDATE, 2 deptno POSITION () integer external (2) NULLIF deptno = BLANKS, 3 job POSITION () char terminated by whitespace nullif job = BLANKS "UPPER (: job) ", mgr POSITION (28:31) integer external terminated by whitespace, NULLIF mgr = blks, ename POSITION (34: 41) char terminated by whitespace" UPPER (: ename )", empno POSITION (45) integer external terminated by whitespace, sal POSITION (51) Char terminated by whitespace "TO_NUMBER (: sal, '$99,999.99')", 4 comm integer external enclosed by '(' AND '%' ": comm * 100 ") specifying the ing between columns and fields we know that the job of SQL * Loader is to load records in data files into database tables, therefore, we must have the correspondence between the record fields of the data file and the columns of the database table. Therefore, we must first configure this relationship in the field list of the control file. Note that all columns in the table do not need to appear in the field list. If the columns do not exist, SQL * Loader will automatically fill the column with NULL. However, a special field is called a resolution field (identified by a FILTER). It does not match the column in the Table. Its main function is to provide the condition judgment basis for the WHEN statement, as shown below: into table dept WHEN recid = 1 (recid filler position () integer external, deptno POSITION () integer external, dname POSITION () CHAR) into table emp WHEN recid <> 1 (recid filler position () integer external, empno POSITION () integer external, ename POSITION () CHAR, deptno POSITION) integer external) specified position (POSI TION) We know that the record is stored in bytes in the data file. If the size of each field in the record is known, we can use the POSITION statement to specify the byte POSITION of the field in the record, the syntax is as follows: Example: ename POSITION () CHAR empno POSITION (22-26) integer external allow POSITION (* + 2) integer external terminated by "/" in the above example, 1 ~ 20 bytes correspond to the ename column, 22 ~ The 26 bytes correspond to the empno column. * indicates that the last byte of the last field starts (27). Therefore, * + 2 = 29, that is, starting from 29th bytes, all bytes until the separator '/' is encountered belong to the allow column. Data Type SQL * Loader reads data file fields based on the data types defined in the control file and sends them to the corresponding columns in the database table. Note the following: the data types defined in the control file do not need to be the same as the corresponding columns in the database, because SQL * Loader will automatically convert, including Character Set conversion. Of course, you must ensure that they are convertible; otherwise, an error is returned. There are two types of control files: Portable and non-portable. The so-called Portable data type is independent of the specific platform, but cannot be transplanted. Portable data types include integer (n), smallint, float, double, byteint, zoned, decimal, vargraphic, varchar, varraw, and long varraw, I use portable data types, so next we will focus on the portable data types: CHAR is the most common and default data type. Its syntax is as follows: length indicates the maximum length of CHAR, if this parameter is not specified, it is 256. It must be separated from the CHAR of the database. The CHAR of SQL * Loader is a variable-length data type, which is somewhat similar to the varchar of the database. DatatimeDATETIMETIME with time interval with time zonetimestamp with local time ZONEIntervalINTERVAL year to monthinterval day to SECONDNumeric EXTERNAL represents numeric data types in character form, including (integer external, float external, decimal external, and zoned external). Its features are similar to CHAR. In actual use, it is generally used to replace unportable numeric data types. The separators CHAR, datetime, interval, and numeric EXTERNAL can be identified by delimiters. The delimiter syntax is as follows: Terminated by and Enclosed by can be used separately or in combination. The following are some examples: 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 settings (WHEN, NULLIF, DEFAULTIF) NULLIF: if the condition is met, it is set to NULL. The following is an example: ull_fieldname... the NULLIF column_name = BLANKS parameter indicates the meaning of white space (excluding ta B). The example above indicates that if the field is "BLANKS", the field is NULL. SQL * Loader generates data. Sometimes we may want some data to be automatically generated during data loading. SQL * Loader provides some parameters for generating data. CONSTANT sets the column value as a CONSTANT. Syntax: column_name CONSTANT value expression sets the column value as the expression value. Syntax: column_name EXPRESSION "SQL string" the current date sets the column value as the current date. Syntax: column_name SYSDATE sequence sets the column value as a unique sequence number. Syntax: