[Oracle] Sql*loader Detailed Usage Tutorials (3)-Control files

The control file is the most important file in Sql*loader, which is a text file that defines the location of the data file, the format of the data, and the behavior of the configuration data loading process, in which the control file is specified in Sqlldr.

Configure command line parameters (OPTIONS) in the control file

In the previous article we talked about the command line parameters can be configured in three places, one of which is the use of control files can be used in the options (which is also the most common way), as follows:

OPTIONS (Direct=true, silent= (ERRORS, FEEDBACK))

For more command-line arguments, see the previous article.

configuration Data file (INFILE)

You can use infile to configure one or more data files with the following syntax:

If the data is contained in the control file itself, it is denoted by *, such as:

INFILE  *

At the end of the control file, start with Begindata, followed by the data:

Begindata data

What if you have more than one data file? Very simple, as long as multiple infile can be, but also for each infile to specify their own error files and discard files, and so on, 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

Character set if the character set of the data file is not the same as the character set of the database, Sql*loader automatically converts the character set of the data file into the database's character set, although the database's character set is a superset of the data file's character set. The character set of the database can be queried by the following SQL statement: [SQL]View PlainCopyprint?

1. SELECT * from nls_database_parameters;

The character set of the data file can be configured with the CharacterSet parameter in the control file, with the following syntax:

If the CharacterSet parameter is not set, the character set of the data file is Nls_lang by the operating system.
Data types affected by the character set are: Char,varchar,numeric EXTERNAL, datetime, Interval

There is also a character set to pay special attention to, is to control the character set of the file itself (can only be set by Nls_lang), if the control file character set and data file is not the same, it will be converted to the data file character set, but it is easy to make mistakes (especially separators), so the actual use for convenience, The Nls_lang,characterset (if any) is generally set to the same as the database character set.

Data Load mode (INSERT, APPEND, REPLACE, TRUNCATE)

By default, Sql*loader is loaded with INSERT, the target table must be an empty table, or it will be an error;

If the target table is a non-empty table, you must specify one of the following three ways:

1. APPEND: Add data to the back of the source data

2. Replace: Before adding data, perform a delete from table to empty the table, note: Replace here is a table-level override instead of a row level

3. TRUNCATE: Before adding data, execute TRUNCATE TABLE table_name Reuse STORAGE

From the above we can know that Sql*loader does not support the existing row updates, if you want to update the existing rows, you must first load the data into an empty intermediate table, and then the original table and the intermediate table to associate the update

If you want to insert a record into more than one table, you can use multiple into tables, for example:

1   manufacturing       -dept record 2 1119 Smith       -emp record 2 1120 Snyder     1   Shipping 2 112 1 Stevens    

The data file, as shown above, is inserted into a different table according to the value of 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,     

Data filtering (when) we can use when words to filter the required records, the records that do not meet when conditions will be discarded, wehn syntax is as follows: Here is an example:

Set the field delimiter (when)

If all the field separators are the same, you can set a default delimiter with the following syntax:

Here is an example:

Fields TERMINATED by whitespace

Unable to match fields with null padding (TRAILING nullcols)

If there are fewer columns in the data file than the target table, it is best to use NULL instead, otherwise the error will be as follows:

into TABLE dept     TRAILING Nullcols (deptno char TERMINATED by "",   dname  char TERMINATED by whitespace,   Lo C    

The recording format is as follows:

For this record above, the LOC column has a value of NULL.

example of a control file

Here is an example of a control file:

[Plain]View PlainCopyprint?

2. OPTIONS (Direct=true,skip_index_maintenance=true,parallel=true)
4. LOAD DATA
6. INFILE ' Nor.dat '
8. Badfile ' Nor.bad '
10. Discardfile ' NOR.DSC '
12. Into TABLE p95169. Disease_expert_relation
14. APPEND
16. When len= ' 3 '
18. Fields TERMINATED by whitespace
20. (
22. Len FILLER POSITION (1) CHAR,
24. Diseaseexpertuuid EXPRESSION "Sys_guid ()",
26. Expertuuid CHAR,
28. Diseaseuuid CHAR,
30. Diseasesortcode EXPRESSION "NULL",
32. Diseasename CHAR,
34. Createdtime EXPRESSION "To_char (sysdate, ' Yyyymmddhh24miss ')",
36. Modifiedtime EXPRESSION "To_char (sysdate, ' Yyyymmddhh24miss ')"
38. )
40. Into TABLE p95169. Disease_expert_relation
42. APPEND
44. When len= ' 2 '
46. Fields TERMINATED by whitespace
48. (
50. Len FILLER POSITION (1) CHAR,
52. Diseaseexpertuuid EXPRESSION "Sys_guid ()",
54. Expertuuid CHAR,
56. Diseaseuuid EXPRESSION "NULL",
58. Diseasesortcode EXPRESSION "NULL",
60. Diseasename CHAR,
62. Createdtime EXPRESSION "To_char (sysdate, ' Yyyymmddhh24miss ')",
64. Modifiedtime EXPRESSION "To_char (sysdate, ' Yyyymmddhh24miss ')"
66. )
68. Into TABLE p95169. Disease_expert_relation
70. APPEND
72. When len= ' 1 '
74. Fields TERMINATED by whitespace
76. (
78. Len FILLER POSITION (1) CHAR,
80. Diseaseexpertuuid EXPRESSION "Sys_guid ()",
82. Expertuuid CHAR,
84. Diseaseuuid EXPRESSION "NULL",
86. Diseasesortcode EXPRESSION "NULL",
88. Diseasename EXPRESSION "NULL",
90. Createdtime EXPRESSION "To_char (sysdate, ' Yyyymmddhh24miss ')",
92. Modifiedtime EXPRESSION "To_char (sysdate, ' Yyyymmddhh24miss ')"
94. )

[Oracle] Sql*loader Detailed Usage Tutorials (1)-Overview [Oracle] Sql*loader Tutorial (2)-command line parameters [Oracle] sql*loader Detailed tutorial (3)-Control file

[Oracle] Sql*loader Detailed Usage Tutorials (4)-field list

Top

6

Step

[Oracle] Sql*loader Detailed Usage Tutorials (3)-Control files