Explain in detail how to use Oracle SQL * Loader

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: datamydata.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, 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) = 19991217

(

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.

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

INFIL