Oracle SQL * Loader User Guide (Part 1)

I have been saving this article for a while and it is very practical. I would like to thank the original author: Angel. John.
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
 
Fixed-level data load can be specified through position
Example:
LOAD DATA
INFILE 'zipcodes. dat'
Replace into table zipcodes (
City_name POSITION (1:10) CHAR (10 ),
Zip_code POSITION (*: 15) CHAR,
State_abbr POSITION (17-18) CHAR
)
 
City_name POSITION (1:10) CHAR (10 ),
The city name begins at position 1 and goes through position 10. In this case,
The length was specified redundantly in the datatype specification. A colon
Was used to separate the beginning and ending values
 
Zip_code POSITION (*: 15) CHAR,
The * indicates that the ZIP Code begins with the first byte following the city
Name. The ending position has been hardcoded as the 15th byte. A colon has
Been used to separate the two values. No length has been specified with
Datatype, so SQL * Loader will compute the length as ending-beginning + 1.
 
State_abbr POSITION (17-18) CHAR
The state abbreviation has been specified in absolute terms. This time,
Hyphen has been used to separate the two values. Again, no length has been
Specified with the datatype, so SQL * Loader will derive the length based on
Beginning and ending values.
 
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.
 
Add new content:
1: When a physical line is broken, you can use continueif last to "merge physical lines"
LOAD DATA
INFILE 'data15. dat'
Replace continueif last = ','
Into table michigan_features
(
Feature_name char terminated by ', 'enabledby '"',
Feature_type char terminated by ', 'enabledby '"',
County char terminated by ', 'enabledby '"',
Latitude char terminated by ', 'enabledby '"',
Longbench char terminated by ', 'enabledby '"',
Elevation integer external terminated by ', 'enabledby '"'
)
Begindata
"Grace Harbor", "bay", "Keweenaw", "475215N ",
"0891330 W", "601"
"Minong Ridge", "ridge", "Keweenaw", "480115N", "0885348 W", "800"
"Siskiwit Lake ",
"Lake ",
"Keweenaw ",
"480002N ",
"0884745 W ",
"659"
 
Example:
SQL> conn hr/hr;
Connected.
SQL> create table michigan_features (feature_name varchar2 (100 ),
2 feature_type varchar2 (100 ),
3 county varchar2 (100 ),
4 latitude varchar2 (100 ),
5 longpolling varchar2 (100 ),
6 elevation number );
 
The table has been created.
 
SQL> host sqlldr hr/hr control = c: \ data \ loader. ctl log = c: \ data \ load. log;
 
SQL * Loader: Release 10.2.0.1.0-Production on Tuesday August 12 16:18:34 2008
 
Copyright (c) 1982,200 5, Oracle. All rights reserved.
 
Submission point reached-logical record count 5
 
 
SQL> select * from michigan_features;
 
FEATURE_NAME
---------------------------------------------------------------------------
 
Grace Harbor
Minong Ridge
Siskiwit Lake
 
2: Split the record field records:
LOAD DATA
INFILE *
Replace into table michigan_features
(
Feature_name char terminated ',',
Feature_type char terminated ',',
County char terminated ',"',
Lat_degrees integer external (2 ),
Lat_minutes integer external (2 ),
Lat_seconds integer external (2 ),
Lat_direction char terminated '","',
Long_degrees integer external (3 ),
Long_minutes integer external (2 ),
Long_seconds integer external (2 ),
Long_direction char terminated '"'
)
Begindata
"Wagner Falls", "falls", "Alger", "462316N", "0863846 W"
"Tannery Falls", "falls", "Alger", "462456N", "0863737 W"
 
Example:
SQL> conn hr/hr
Connected.
SQL> set wrap off
SQL> create table michigan_features (
2 feature_name varchar2 (100 ),
3 feature_type varchar2 (100 ),
4 county varchar2 (100 ),
5 lat_degrees number,
6 lat_minutes number,
7 lat_seconds number,
8 lat_direction varchar2 (100 ),
9 long_degrees number,
10 long_minutes number,
11 long_seconds number,
12 long_direction varchar2 (100 ));
 
The table has been created.
 
SQL> host sqlldr hr/hr control = c: \ data \ loader. ctl log = c: \ data \ load. log;
 
SQL * Loader: Release 10.2.0.1.0-Production on Tuesday August 12 16:42:57 2008
 
Copyright (c) 1982,200 5, Oracle. All rights reserved.
 
Commit point reached-logical record count 2
 
SQL> select * from michigan_features;
The row is truncated.
 
 
FEATURE_NAME
--------------------------------------------------------------------------------
 
"Wagner Falls"
"Tannery Falls"
 
3. Use an SQL expression. In this example, the to_number function is used. You can also use a custom function.
LOAD DATA
INFILE *
Replace into table book
Fields terminated by "," optionally enclosed '"'
(
Book_title,
Book_price
"GREATEST (TO_NUMBER (: book_price)/100 * TO_NUMBER (: book_pages * 0.10 ))",
Book_pages
)
Begindata
Oracle Essentials, 3495,355
SQL * Plus: The definition Guide, 3995,502
Oracle PL/SQL Programming, 4495, 87
Oracle8 Design Tips, 1495,115
 
Example:
 
SQL> create table BOOK
2 (
3 BOOK_ID NUMBER,
4 BOOK_TITLE VARCHAR2 (35 ),
5 BOOK_PRICE NUMBER,
6 BOOK_PAGES NUMBER
7 );
 
The table has been created.
 
SQL> sqlldr hr/hr control = c: \ data \ loader. ctl log = c: \ data \ log. log
SP2-0734: Unknown command starting with "sqlldr hr/..."-ignore the remaining lines.
SQL> host sqlldr hr/hr control = c: \ data \ loader. ctl log = c: \ data \ log. log;
 
SQL * Loader: Release 10.2.0.1.0-Production on Thursday August 14 09:19:03 2008
 
Copyright (c) 1982,200 5, Oracle. All rights reserved.
 
Commit point reached-logical record count 3
Submission point reached-logic record count 4
 
SQL> select * from book;
 
BOOK_ID BOOK_TITLE BOOK_PRICE BOOK_PAGES
-----------------------------------------------------------------
Oracle Essentials 1240.725 355
SQL * Plus: The Definitive Guide 2005.49 502
Oracle PL/SQL Programming 391.065 87
Oracle8 Design Tips 171.925 115
 
SQL>
 
4. String Conversion:
The following LOAD statement provides an example of how to specify a character
Set. In this case, the character set is an EBCDIC character set named
WE8EBCDIC37C. Look for the CHARACTERSET clause in the second line of
LOAD command:
LOAD DATA
CHARACTERSET 'we8ebcdic37c'
INFILE 'book _ prices. dat'
Replace into table book
(
Book_title POSITION (1) CHAR (35 ),
Book_price POSITION (37)
"GREATEST (TO_NUMBER (: book_price)/100,
TO_NUMBER (: book_pages * 0.10 ))",
Book_pages POSITION (42) integer external (3 ),
Book _
Due to insufficient permissions, this article will be issued in two parts. For more information, see Oracle SQL * Loader User Guide (Part 2)

This article is from the "Dog's nest"