Shulou(Shulou.com)05/31 Report--

How to understand SQLLDR-CTL files, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain for you in detail, people with this need can come to learn, I hope you can gain something.

SQLLDR can import some data stored in text format into oracle database smoothly, which is a very convenient and universal tool for data migration between different databases. The disadvantage is that the speed is relatively slow, and it is not convenient for blob and other types of data.

Introduction to CTL file

The first part

1. LOAD DATA: it usually starts with this, and can be preceded by the following parameters:

2. UNRECOVERABLE: data is unrecoverable

3. RECOVERABLE: indicates that the data can be recovered

4. CONTINUE_LOAD: continue to add

Part II

1. INFILE: indicates the location of the data file. If the value is *, the data is in the control file. In this case, there is no separate data file. For most loads, the data file will be separated from the control file.

2. INTO TABLE tbl_name:tbl_name is the target table into which the data is loaded, which must be created before you execute the SQLLDR command.

3. There are some interesting parameters to specify before INTO.

4. INSERT: insert data into the table. The table must be empty. If the table is not empty, an error will be reported when executing the SQLLDR command. The default is the INSERT parameter.

5. APPEND: appends data to the table, regardless of whether there is data in the table or not.

6. REPLACE: replace the data in the table, which is equivalent to all the data in the DELETE table first, and then INSERT.

7. TRUNCATE: similar to REPLACE, except that instead of using DELETE to delete the data in the table, it is deleted by TRUNCATE, and then INSERT.

8. FIELDS TERMINATED BY ",": sets the delimited value of the string in the data section, which is set here to be separated by a comma (,). Of course, it can also be changed to any other visible character, as long as you make sure it is the delimiter in the data row.

9. (ENAME, JOB, SAL): the column name of the table to be inserted. It should be noted here that the column name should be exactly the same as the column name in the table, and the order of the columns can be different from that of the columns in the table, but it must correspond to the columns in the data part one by one.

10. The position keyword is used to specify the start and end positions of the column

11. Position (MVR n): starting from the m character and ending to the nth character as the column value

12. Position (* + 2:15): the way to directly specify a numeric value is called an absolute offset. If you use the * sign, it is a relative offset, indicating that where the previous field ends, it starts this time, and the relative cheap amount can also be calculated again.

13. Position (*) char (9): the advantage of this relative offset + type and length is that you only need to specify the start position for the first column and only the column length for the other columns.

14. FILLER: FILLER is specified in the control file, indicating that the column values are not imported into the table.

15. BEGINDATA: indicates that the following data is to be loaded, which is valid only if INFILE is specified as *

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.