Oracle Sql*loader Use Guide (reprint)

Oracle Sql*loader is a tool for Oracle databases to import external data. It is similar to the DB2 load tool, but with more options, it supports varying loading modes, optional loading and multiple table loading.


How to use the Sql*loader tool
We can use Oracle's SQLLDR tools to import data. For example:
Sqlldr Scott/tiger Control=loader.ctl
The control file (LOADER.CTL) loads an external data file (including delimiters). Loader.ctl is as follows:
Load data
InFile ' C:\data\mydata.csv '
into table emp
Fields terminated by "," optionally enclosed by ""
(Empno, EmpName, Sal, Deptno)

Mydata.csv is as follows:
10001, "Scott Tiger," 1000, 40.
10002, "Frank Naude", 500, 20.
The following is a sample control file that specifies the length of the record. "*" represents the data file with the same name as this file, that is, the Begindata segment is used later to identify the data.
Load data
InFile *
Replace
into table Departments
(Dept position (02:05) char (4),
Deptname position (08:27) char (20)
)
Begindata
COSC COMPUTER Science
ENGL 中文版 Literature
MATH Mathematics
POLY Political Science
Unloader such a tool
Oracle does not provide a tool to export data to a file. However, we can use the Sql*plus select and format data to output to a file:
Set echo off NewPage 0 spaces 0 pagesize 0 feeds off head off Trimspool on
Spool Oradata.txt
Select Col1 | | ',' || col2 | | ',' || Col3
From TAB1
where col2 = ' XYZ ';
Spool off

Alternatively, you can use Utl_file pl/sql package processing:
REM Remember to update Initsid.ora, utl_file_dir= ' c:\oradata ' parameter
Declare
FP Utl_file.file_type;
Begin
fp: = Utl_file.fopen (' C:\oradata ', ' tab1.txt ', ' W ');
Utl_file.putf (FP, '%s,%s\n ', ' TextField ', 55);
Utl_file.fclose (FP);
End
/

Of course, you can also use third-party tools, such as Sqlways, TOAD for Quest, and so on.

Load variable-length or specified-length records
Such as:
LOAD DATA
INFILE *
Into TABLE Load_delimited_data
FIELDS terminated by "," optionally enclosed by ""
Trailing Nullcols
(Data1,
Data2
)
Begindata
11111,aaaaaaaaaa
22222, "A,b,c,d,"

The following is an example of importing fixed-position (fixed-length) data:
LOAD DATA
INFILE *
Into TABLE Load_positional_data
(Data1 POSITION (1:5),
Data2 POSITION (6:15)
)
Begindata
11111AAAAAAAAAA
22222BBBBBBBBBB

Skipping data rows:
You can use the "SKIP n" keyword to specify how many rows of data can be skipped when importing. Such as:
LOAD DATA
INFILE *
Into TABLE Load_positional_data
SKIP 5
(Data1 POSITION (1:5),
Data2 POSITION (6:15)
)
Begindata
11111AAAAAAAAAA
22222BBBBBBBBBB

To modify data when importing data:
When you import data to a database, you can modify the data. Note that this is only suitable for regular imports and is not appropriate for direct import. For example:
LOAD DATA
INFILE *
Into TABLE Modified_data
(rec_no "My_db_sequence.nextval",
Region CONSTANT ' 31 ',
Time_loaded "To_char (sysdate, ' Hh24:mi ')",
Data1 POSITION (1-5) ":d ata1/100",
Data2 POSITION (6:15) "Upper (:d ata2)",
Data3 POSITION (16:22) "To_date (:d ata3, ' YYMMDD ')"
)
Begindata
11111aaaaaaaaaa991201
22222bbbbbbbbbb990112

LOAD DATA
INFILE ' Mail_orders.txt '
Badfile ' Bad_orders.txt '
APPEND
Into TABLE mailing_list
FIELDS terminated by ","
(Addr,
City
State
ZipCode
Mailing_addr "Decode (: Mailing_addr, NULL,: addr,: mailing_addr)",
Mailing_city "Decode (: Mailing_city, NULL,: City,: mailing_city)",
Mailing_state
)

To import data into multiple tables:
Such as:
LOAD DATA
INFILE *
REPLACE
into TABLE EMP
When Empno!= '
(Empno POSITION (1:4) INTEGER EXTERNAL,
Ename POSITION (6:15) CHAR,
Deptno POSITION (17:18) CHAR,
Mgr POSITION (20:23) INTEGER EXTERNAL
)
Into TABLE proj
When Projno!= '
(Projno POSITION (25:27) INTEGER EXTERNAL,
Empno POSITION (1:4) INTEGER EXTERNAL
)

To import Selected records:
The following example: (01) represents the first character, (30:37) representing the characters between 30 and 37:
LOAD DATA
INFILE ' Mydata.dat ' badfile ' Mydata.bad ' discardfile ' Mydata.dis '
APPEND
Into TABLE my_selective_table
When (a) <> ' H ' and (a) <> ' T ' and (30:37) = ' 19991217 '
(
Region CONSTANT ' 31 ',
Service_key POSITION (01:11) INTEGER EXTERNAL,
Call_b_no POSITION (12:29) CHAR
)

Some fields are skipped when importing:
Postion (x:y) can be used to separate data. Can be implemented in oracle8i by specifying the filler field. The filler field is used to skip and ignore fields in the import data file. For example:
LOAD DATA
TRUNCATE into TABLE T1
FIELDS terminated by ', '
(Field1,
Field2 Filler,
Field3
)

To import multiple-line records:
You can use one of the following two options to import multiple rows of data into one record:

Concatenate:-Sql*loader should combine the same number of physical records to form one together record.

Continueif-use If a condition indicates that multiple records should is treated as one. Eg. By has a ' # ' character in column 1.

Submission of Sql*loader data:
Typically, it is submitted after data file data has been imported.
You can also specify the number of records to commit each time by specifying the rows= parameter.

Improve the performance of Sql*loader:
1 A simple and easy to ignore problem is that no indexes and/or constraints (primary key) are used for imported tables. If you do this, you can significantly degrade database import performance even when you use the rows= parameter.
2 can add direct=true to improve the performance of the imported data. Of course, in many cases, this parameter cannot be used.
3 You can close the log of the database by specifying the unrecoverable option. This option can only be used with direct.
4 You can run multiple import tasks at the same time.

The difference between the general import and direct import methods:
A general import can import data by using an INSERT statement. Direct import can skip the relational logic (direct=true) of the database and import the data directly into the data file.