Shulou(Shulou.com)06/01 Report--

The previous summary of the usage of SQL*Loader is used again today, and we will find out again.

SQL*Loader loads data from an external file into a table in Oracle DB. It has a powerful data analysis engine, so there is no restriction on the format of the data in the data file.

SQL*Loader uses the following files:

Input data file: SQL*Loader reads data from one or more files (or operating system equivalent files) specified in the control file. From the perspective of SQL*Loader, the data in the data file is organized by record. A specific data file can be in fixed record format, variable record format, or stream record format. The record format can be specified by the INFILE parameter in the control file. If no record format is specified, the default format is the stream record format.

Control file: the control file is a text file that is written in a language recognized by SQL*Loader. The control file instructs SQL*Loader where to find data, how to analyze and interpret the data, where to insert data, and so on. The control file is an imported template.

Log file: when SQL*Loader starts execution, a log file is created. If the log file cannot be created, execution is terminated. The log file contains a detailed description of the load operation, including a description of any errors that occur during the load process.

Bad files: bad files contain records that are rejected by SQL*Loader or Oracle DB. When the input format is invalid, SQL*Loader rejects the data file record. After SQL*Loader accepts that a datafile record is processed, it sends the datafile record to Oracle DB so that it can be inserted into the table as a row. If Oracle DB determines that the row is valid, it inserts the row into the table; if it determines that the row is invalid, it rejects the record, and SQL*Loader places the record in a bad file.

Undo file: this file is created only if such a file is required and you specify that undo files should be enabled. The records contained in the discard file are filtered from the load because they do not meet any of the record selection criteria specified in the control file.

The last three may not be selected.

The steps are as follows:

1) to create the data file, I am using the emp table emp.csv derived from plsql developer.

The contents are as follows:

2) the target table must exist and be consistent with the field definition of the data file, otherwise an error will occur. Here I use as select to create a table emp3 from the emp table, and then use the delete statement to empty the table data.

3), create a control file user.ctl. The control file I have here is generated by me with the EM tool. The contents are as follows:

OPTIONS (skip=1,rows=128) LOAD DATA INFILE'/ u01 *

Note:

The 1.LOAD DATA statement tells SQL*Loader to start a new data load operation

The 2.INFILE keyword specifies the name of the data file containing the data to be loaded. You can also use BADFILE and DISCARDFILE to specify bad data and files to discard data

The 3.APPEND keyword is one of the options you can use when loading data into a non-empty table. To load data into an empty table, use the INSERT keyword.

The table into which 4.INTO TABLE inserts the record

5.Fields terminated by "," defines that each row of records in the data is separated by ","

6.Optionally enclosed by''--each field in the data is framed with a', for example, when there is a "," delimiter in the field

7. At the first line, OPTIONS,skip=1 skips the first line, and rows=128 defines the limit on the number of rows to load.

4), execute the command:

Sqlldr system/oracle control=user.ctl

The screenshot is as follows:

5), query verification

For more usage of sqlldr, please search the Internet by yourself.

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.