Oracle SQL * loader User Guide

SQL * loader is a tool used to import external data from Oracle databases. it is similar to the load tool of DB2, but has more options. It supports variable loading modes, optional loading and multi-Table loading.

How to use SQL * Loader
We can use the sqlldr tool of Oracle to import data. For example:
Sqlldr Scott/tiger control = loader. CTL
The control file (loader. CTL) will load an external data file (including separators). loader. CTL is as follows:
Load data
Infile 'C:/data/mydata.csv'
Into Table EMP
Fields terminated by "," optionally enclosed '"'
(Empno, empname, Sal, deptno)

Mydata.csv is as follows:
10001, "Scott tiger", 1000, 40
10002, "Frank naude", 500, 20
The following is an example control file that specifies the record length. "*" Indicates that the data file has the same name as this file, that is, the begindata segment is used later to identify the data.
Load data
Infile *
Replace
Into Table orders ments
(Dept position (0: 05) Char (4 ),
Deptname position (08:27) Char (20)
)
Begindata
Cosc Computer Science
Engl English Literature
Math mathematics
Poly Political Science
Unloader
Oracle does not provide a tool to export data to a file. However, we can use SQL * Plus's select and format data to output to a file:
Set echo off Newpage 0 space 0 pagesize 0 feed off head off trimspool on
Spool oradata.txt
Select col1 | ',' | col2 | ',' | col3
From tab1
Where col2 = 'xyz ';
Spool off

You can also use utl_file PL/SQL package for 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;
/

You can also use third-party tools, such as sqlways and toad for quest.

Load records of variable length or specified length
For example:
Load data
Infile *
Into Table load_delimited_data
Fields terminated by "," optionally enclosed '"'
Trailing nullcols
(Data1,
Data2
)
Begindata
11111, aaaaaaaaaa
22222, "a, B, c, d ,"

The following is an example of importing data with a fixed position (fixed length:
Load data
Infile *
Into Table load_positional_data
(Data1 position (1:5 ),
Data2 position (6: 15)
)
Begindata
11111 aaaaaaaaaa
22222 bbbbbbbbbb

Skip data rows:
You can use the "Skip N" keyword to specify the number of rows of data that can be skipped during import. For example:
Load data
Infile *
Into Table load_positional_data
Skip 5
(Data1 position (1:5 ),
Data2 position (6: 15)
)
Begindata
11111 aaaaaaaaaa
22222 bbbbbbbbbb

Modify data when importing data:
You can modify the data when importing data to the database. Note: This method is only applicable to regular imports and is not suitable 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 () ": data1/100 ",
Data2 position (6: 15) "upper (: data2 )",
Data3 position (16: 22) "to_date (: data3, 'yymmdd ')"
)
Begindata
11111aaaaaaaaaa991201
22222bbbbbbbbbb990112

Load data
Infile 'mail_orders.txt'
Badfile 'bad_orders.txt'
Append
Into Table mailing_list
Fields terminated ","
(ADDR,
City,
State,
Zipcode,
Mailing_addr "decode (: mailing_addr, null,: ADDR,: mailing_addr )",
Mailing_city "decode (: mailing_city, null,: City,: mailing_city )",
Mailing_state
)

Import data to multiple tables:
For example:
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
)

Import selected records:
For example, (01) represents the first character, and (30: 37) represents the character between 30 and 37:
Load data
Infile 'mydata. dat 'badfile' mydata. bad' discardfile 'mydata. Dis'
Append
Into Table my_selective_table
When (01) <> 'H' and (01) <> 'T' and (30: 37) = '123'
(
Region constant '31 ',
Service_key position (0:11) integer external,
Call_ B _no position (12: 29) Char
)

Skip some fields during import:
Data can be separated by postion (X: Y). You can specify the filler field in Oracle8i. The filler field is used to skip or ignore fields in the imported data file. For example:
Load data
Truncate into Table T1
Fields terminated ','
(Field1,
Field2 filler,
Field3
)

Import multiple rows of records:
You can use one of the following two options to import multiple rows of data into a record:

Concatenate:-use when SQL * loader shoshould combine the same number of physical records together to form one logical record.

Continueif-use if a condition indicates that multiple records shoshould be treated as one. eg. By having a' # 'character in column 1.

SQL * loader data submission:
Generally, it is submitted after the data file is imported.
You can also specify the number of records submitted 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 keys) are used for the imported tables ). If this is done, the Database Import performance will be significantly reduced even when the rows = parameter is used.
2) You can add direct = true to Improve the Performance of imported data. Of course, this parameter cannot be used in many cases.
3) you can disable database logs by specifying the unrecoverable option. This option can only be used with direct.
4) You can run multiple import tasks at the same time.

Differences between conventional import and direct import:
You can use the insert statement to import data for regular import. Direct import can skip the related database logic (direct = true) and directly import the data to the data file.