Sql*loader importing data from a text file to a database

Before my blog once wrote how to use Toad this tool from Excel or CSV file to import data into the database. In fact, Oracle itself provides such a tool, called Sql*loader. This tool is less straightforward and error-prone, but it is ideal for importing large amounts of text. Execution is very efficient, claiming that an hour can be imported into the G (heard).

Here is a simple example of how to use it.

For example, a client wants to import data into Oracle's EBS from the ERP software he used before. Of course, he could not directly copy the table to come over, after all, two software table structure is different. Then he needs to import the data into the EBS interface table. Now he wants to import the data into the INV Module interface table MTI inside. This is what he needs two files. A file is a. dat file that contains all the data that needs to be imported, which can be very large. The other is the control file, the. ctl file, which is stored in the import law. When all two files are ready, run the following command:

Sqlldr Apps/apps Control=***.ctl Data=***.dat

The SQLLDR command is the utility that invokes the Sql*loader, which has the option to import the data in the. dat file according to the rules defined by the. CTL control file.

The data file might be like this:

INVENTORY_RECEIPT_IFD DCS INV-RCV 0000000000003791368058627 WN                     oa133-1-141113 oa133-1-141113 WLN AVAILABLE       0000000018 EAWLG 000200010000000018 395                           X0 0.00wmd120141113142153intransit FGI                                                                                                                                                                                                                                              395                         

There are a lot of spaces, and each space occupies one character, which is necessary because it is written in the control file:

Load Dataappendinto Table Inv. Mtl_transactions_interfacewhen (1:21 = ' inventory_receipt_ifd ') and (35:41 = ' INV-RCV ') and (372:372 = ' X ') (TRANSACTION              _interface_id "Mtl_material_transactions_s.nextval", transaction_header_id CONSTANT ' 0 ', creation_date            Sysdate, created_by CONSTANT ' 1198 ', Last_update_date sysdate, last_updated_by CONSTANT ' 1198 ', Source_code CONSTANT ' WMS WI mrecl ', source_line_id CONSTANT           ' 0 ', source_header_id CONSTANT ' 0 ', Process_flag CONSTANT ' 1 ', Transaction_mode CONSTANT ' 3 ', transaction_type_id CONSTANT ' 2 ', transaction_action_id CONSTANT ' ", TRA nsaction_source_type_id CONSTANT ' + ', organization_id POSITION (492:501), Transfer_organization POS   Ition (362:371), Transaction_source_name POSITION (95:104), Vendor_lot_number POSITION (129:148)                             "Replace (: Vendor_lot_number, '-')", Transaction_date POSITION (418:431) DATE ' YYYY Mmddhh24miss ', transaction_quantity POSITION (254:263), Transfer_subinventory POSITION (462:471), Subi Nventory_code POSITION (432:441), Loc_segment1 POSITION (149:168) "substr (: LOC          _segment1, 1,instr (: Loc_segment1, '-')-1) ", Item_segment1 POSITION (65:94), Transaction_uom POSITION (298:299) nullif (Transaction_uom = BLANKS), ATTRIBUTE1 Positio N (300:303), ATTRIBUTE3 POSITION (125:128), Shipment_number POSITION (169:203), TRA Nsaction_reference POSITION (45:64) "LTrim (: Transaction_reference, ' 0 ')")

It uses the character position to get the data in the data file.

If you do not pass the character position, you will need a delimiter in the data file, usually using commas. A CSV file is a typical file that uses commas as delimiters, so it is well suited as a data file format.

Writing a control file is a very complex thing, and if you use a character position to position it as in the example above, it is very error prone. Oracle provides a script that can automatically generate control files. Refer to Note 1019523.6

Set echo offset heading offset Verify offset feedback offset show offset trim offset pages 0set concat onset lines 300set Trimspool onset trimout onspool &1..ctlselect ' LOAD DATA ' | |       Chr (10) | | ' INFILE ' | | LOWER (table_name) | | '. Dat ' | |       Chr (10) | | ' Into TABLE ' | | table_name| |       Chr (10) | | ' Fields TERMINATED by ', ' ' | |       Chr (10) | | ' TRAILING nullcols ' | | Chr (10) | |       (' from all_tableswhere table_name = UPPER (' &1 '); Select Decode (rownum, 1, ', ', ') | |       Rpad (column_name, 33, ") | | Decode (data_type, ' VARCHAR2 ', ' CHAR nullif (' | | column_name| | ' =blanks) ', ' FLOAT ', ' DECIMAL EXTERNAL nullif (' | | column_name| | ' =blanks) ', ' Number ', decode (data_precision, 0, ' INTEGER EXTERNAL nullif (' | | | column_name| | ' =blanks) ', decode (data_scale, 0, ' INTEGER EXTERNAL nullif (' | | column_name| | ' =blanks) ', ' DECIMAL EXTERNAL nullif (' | | column_name| | '        =blanks)),                   ' Date ', ' Date ' mm/dd/yy ' Nullif (' | | column_name| | ' =blanks) ', null ' from user_tab_columnswhere table_name = UPPER (' &1 ') Order by Column_id;select ') ' from Sys.dual;s Pool off

Using Sql*plus to run this script, a. ctl file is generated in the current directory, which is the control file, and the file delimiter is a comma.

Sql*loader importing data from a text file to a database