How to Use SqlLoader,

How to Use SqlLoader,

SQL * Loader (SQLLDR) is an Oracle high-speed Batch Data Loading tool. This is a very useful tool for loading data to the Oralce database in multiple flat file formats. Today, I applied for the use of * loader. I tried it and recorded it here.

1. Assume that the data table ftest is to be inserted. The field is (id, username, password, sj)

2. The imported table data is stored in txtformat and named as data.txt.

 1 f f 2010-8-192 f1 f1 2010-8-193 f2 f2 2010-8-194 f3 f3 2010-8-195 f4 f4 2010-8-19

3. Write control file, in the format of ctl, named as cont. ctl. The content is as follows:

 load data　　　　　　　　　　infile 'c:\data.txt'　　　　　　 insert into table ftest　　　　fields terminated by " "(id,username,password,sj)

NOTE: If there is no data in the table, insert is used. If there is data, append is used. delete old data and insert new data with replace or truncate.

4. Execute the command in the cmd command window

sqlldr fyzh/fyzh control=c:\cont.ctl data=c:\data.txt

5. view the table ftest in plsql

Check that the insert operation is successful.

Re-learning sqlldr

A simple example of sqlldr data import:

Load datainfile * -- tells sqlldr that the data to be loaded is included in the control file itself into table dept -- the table to which the file is loaded fields terminated ', '-- the data loading format should be the values separated by commas (deptno, dname, loc) -- the column to be loaded begindata -- tells sqlldr that 10 of the data to be loaded to the dept table, sales, Virginia20, Accounting, Virginia30, Consulting, Virginia40, Finance, destination table dept (deptno number (2) constraint dept_pk primary key, dname varchar2 (14), loc varchar2 (13 )) sqlldr userid = gwm/gwm @ fgisdb control = c: \ demol. ctlselect * from dept; 1 10 Sales Virginia2 20 Accounting Virginia3 30 Consulting Virginia4 40 Finance Virginia

Sqlldr can be imported in four ways:

APPEND: add data in the original table to the end.
INSERT: loads empty tables. If the original table has data, sqlloader will stop the default value.
REPLACE: All data in the original table will be deleted.
TRUNCATE: if the specified content is the same as that of replace, the truncate statement is used to delete existing data.

FAQs about using SQLLDR to load data

1. How to load bounded data

1) bounded data is the data separated by a special character, which may be enclosed by quotation marks. This is the most common data format for flat files.
For bounded data, the most common format is the comma-separated value format. In this file format, each field in the data is separated by a comma. Text strings can be enclosed by quotation marks, so that the strings themselves contain commas. If the string must also contain quotation marks, two quotation marks are generally used. When the bounded data is loaded, the corresponding typical control file is similar to the previous example. However, the fields terminated by clause is usually specified as follows:

 fields terminated by ',' optionally enclose by '"'

It specifies that data fields are separated by commas (,). Each field can be enclosed in double quotation marks. If you modify the last part of the control file as follows:

  fields terminated by ',' optionally enclosed by '"'  (deptno,dname,loc)   begindata       10,Sales,"Virginia,USA"  20,Accounting,"Va,""USA"""  30,Consulting,Virginia  40,Finance,Virginiaselect * from dept1  10  Sales  Virginia,USA2  20  Accounting  Va,"USA"3  30  Consulting  Virginia 4  40  Finance  Virginia

2) Another common format is tab-bounded data. There are two ways to use the terminated by clause to load such data:

Terminated by X '09' -- use a hexadecimal tab; If ASCII is used, the tab should be 9

Terminated by whitespace -- use terminated by whitespaceload datainfile * into table deptreplacefields terminated by whitespace (deptno, dname, loc) begindata 10 Sales Virginia select * from dept; 1 10 Sales Virginia -- use terminated by X '09' load datainfile * into table deptreplacefields terminated by X '09' (deptno, dname, loc) begindata 10 Sales Virginiaselect * from dept; 1 10

Sales -- because once a tab is displayed, a value is output.

Therefore, if 10 is assigned to deptno, The dname returns null because there is no data between the first and second tabs.

3) use the filler keyword of sqlldr

Such as skipping tabs

load datainfile *into table deptreplacefields terminated by X'09'(deptno,dummy1 filler,dname,dummy2 filler,loc) begindata     10        Sales        Virginiaselect * from dept;1  10  Sales  Virginia

2. How to load fixed-format data

To load fixed-position data with a fixed width, the position keyword is used in the control file.

load datainfile *into table deptreplace(deptno position(1:2), dname position(3:16), loc position(17:29) ) begindata     10Accounting   Virginia,USAselect * from dept;1  10  Accounting   Virginia,USA

This control file does not use the terminated by clause; instead, it uses position to tell the sqlldr field where it starts and ends.
For position, we can use overlapping positions, which can be repeated in records. Modify the dept table as follows:

alter table dept add entire_line varchar(29);

Use the following control file:

load datainfile *into table deptreplace(deptno position(1:2), dname position(3:16), loc position(17:29), entire_line position(1:29) ) begindata     10Accounting   Virginia,USAselect * from dept;1  10  Accounting   Virginia,USA  10Accounting  Virginia,USA

When position is used, you can use relative offset or absolute offset. In the preceding example, an absolute offset is used to specify the start and end of a field.
The preceding control file is rewritten as follows:

load datainfile *into table deptreplace(deptno position(1:2), dname position(*:16), loc position(*:29), entire_line position(1:29) ) begindata     10Accounting   Virginia,USA

* Indicates the control file to determine where the last field ends. Therefore, in this case, (*: 16) is the same as (3: 16. Note that the control file can use both relative and absolute positions.
In addition, when using the * notation, you can add it to the offset. For example, if dname is from the end of deptno, it can start with two characters (* +), that is, equivalent ).

The end position in the position clause must be the absolute column position for data termination. Sometimes it is easier to specify the length of each field, especially if these fields are continuous. This

Method, you only need to tell sqlldr: The record starts from the first byte, and then specify the length of each field. As follows:

load datainfile *into table deptreplace(deptno position(1) char(2), dname position(*) char(14), loc position(*) char(13), entire_line position(1) char(29) ) begindata     10Accounting   Virginia,USA select * from dept;

3. how to load a date

To load a date using sqlldr, you only need to specify the date data type in the control file and the date mask to be used. The date mask is the same as the date mask used in to_char and to_date in the database.

Modify the dept table as follows:

alter table dept add last_updated date;load datainfile *into table deptreplacefields terminated by ','(deptno, dname, loc, last_updated date 'dd/mm/yyyy' ) begindata     10,Accounting,Virginia,1/5/2000select * from dept;1  10  Accounting   Virginia    2000-5-1

4. How to load data using functions

To ensure that the loaded data is in uppercase, rewrite the control file as follows:

load datainfile *into table deptreplacefields terminated by ','(deptno, dname "upper(:dname)", loc "upper(:loc)", last_updated date 'dd/mm/yyyy' ) begindata     10,Accounting,Virginia,1/5/2000select * from dept;1  10   ACCOUNTING   VIRGINIA    2000-5-1

The following control files cannot be imported when loading data:

load datainfile *into table deptreplacefields terminated by ','(deptno, dname "upper(:dname)", loc "upper(:loc)", last_updated date 'dd/mm/yyyy', entire_line ":deptno||:dname||:loc||:last_updated" ) begindata     10,Accounting,Virginia,1/5/2000

1) Use of trailing nullcols: it is usually good by default.

The solution is to use trailing nullcols. In this way, if data in a column does not exist in the input record, sqlldr binds a null value to the column.

In this case, adding trailing nullcols will bind the variable: entire_line to null.

load datainfile *into table deptreplacefields terminated by ','TRAILING NULLCOLS(deptno, dname "upper(:dname)", loc "upper(:loc)", last_updated date 'dd/mm/yyyy', entire_line ":deptno||:dname||:loc||:last_updated" ) begindata     10,Accounting,Virginia,1/5/2000select * from dept;1  10  ACCOUNTING  VIRGINIA  10AccountingVirginia1/5/2000  2000-5-1

2) Use case in sqlldr

Assume that the input file contains dates in the following format:
HH24: MI: SS: Only one time. The default date is sysdate.
DD/MM/YYYY: Only one date. The default time is midnight.
HH24: MI: ss dd/MM/YYYY: the date and time are explicitly provided.

The following control files are available:

load datainfile *into table deptreplacefields terminated by ','TRAILING NULLCOLS(deptno, dname "upper(:dname)", loc "upper(:loc)", last_updated "case  when length(:last_updated)>9 then to_date(:last_updated,'hh24:mi:ss dd/mm/yyyy') when instr(:last_updated,':')>0 then to_date(:last_updated,'hh24:mi:ss') else to_date(:last_updated,'dd/mm/yyyy') end" ) begindata10,Sales,Virginia,12:03:03 17/10/200520,Accounting,Virginia,02:23:5430,Consulting,Virginia,01:24:00 21/10/200640,Finance,Virginia,17/8/2005alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss';select * from dept;

5. How to load data with built-in line breaks

1) use non-line breaks to represent line breaks, and use an SQL function to replace the text with a CHR (10) during loading.

Alter table dept add comments varchar2 (4000); -- load the text load datainfile * into table deptreplacefields terminated by ', 'trailing nullcols (deptno, dname "upper (: dname) ", loc" upper (: loc) ", comments" replace (: comments, '\ n', chr (10 )) "-- '\ n' is replaced by chr (10).) begindata10, Sales, Virginia, this is the sales \ noffice in Virginia

Note: \ n must be used to represent the replacement character in the call, instead of \ n.

2) use the FIX attribute on the infile command to load a fixed-length flat file.
Using this method, the input data must appear in a fixed-length record. For data at a fixed position, it is particularly appropriate to use the FIX attribute. These files are generally fixed-length files.
In addition, data must be stored externally and cannot be stored in the control file itself.

-- Control File load datainfile demo. dat "fix 80" -- specifies the demo of the input data file. dat, each record in this file is 80 bytes into table deptreplacefields terminated by ', 'trailing nullcols (deptno, dname "upper (: dname)", loc "upper (: loc )", comments) -- data file 10, Sales, Virginia, this is the sales \ noffice in Virginia 20, Sales, Virginia, this is the sales \ noffice in Virginia

Note:

On unix, the row end mark is \ n, that is, CHR (10), while on windows nt platform, the row end mark is \ r \ n, that is, CHR (13) | CHR (10 );
You can use the trim built-in SQL function in the control file to Remove trailing spaces.

select * from dept;

3) use the VAR attribute in and on the infile command to load a widening file. In the format used by this file, the length of the row is specified in the first few bytes of each row.

-- Control File load datainfile demo. dat "var 3" -- indicates the number of bytes used to record each row. into table deptreplacefields terminated by ', 'trailing nullcols (deptno, dname "upper (: dname) ", loc" upper (: loc) ", comments) -- data file 05410, Sales, Virginia, this is the sales office in Virginia

Note: line breaks on unix are regarded as only one byte, and two bytes are counted on windows nt.

select * from dept;

4) use the STR attribute on the infile command to load a widening file. A Character Sequence is used to represent the row Terminator instead of a line break.
The STR attribute is specified in hexadecimal notation. To obtain a hexadecimal string, the easiest way is to use SQL and utl_raw to generate a hexadecimal string. For example, on unix platforms, the row end mark is CHR (10), and our special character is a pipe sign (|), which can be written:

Select utl_raw.cast_to_raw ('| chr (10) from dual; -- it can be seen that x '7c0a' is displayed on unix'

On windows

Select utl_raw.cast_to_raw ('| chr (13) | chr (10) from dual; -- x '7c0d0a' -- Control File load datainfile demo. dat "str x '7c0d0a'" into table deptreplacefields terminated by ', 'trailing nullcols (deptno, dname "upper (: dname)", loc "upper (: loc )", comments) -- data file 10, Sales, Virginia, this is the salesoffice in Virginia | select * from dept;

6. Load lob data

1) load the inline lob data. These lob data is usually embedded with line breaks and other special characters

-- Modify the depttruncate table dept; alter table dept drop column comments; alter table dept add comments clob; -- data file 10, Sales, Virginia, this is the salesoffice in Virginia | 20, accounting, Virginia, this is the Accountingoffice in Virginia | 30, Consuling, Virginia, this is the Consulingoffice in Virginia | 40, Finance, Virginia, "this is the Financeoffice in Virginia, it has embedded commas and ismuch longer than the othe R comments filed. If youfeel the need to add double quotes text in here likethis: "" you will need to double up those quotes! "" Topreserve them in the string. this field keeps going for up to000000 bytes (because of the control file definition I used) or until we hit the magic and of record marker, the | followed by an end of line-it is right here-> "| -- Control File load datainfile demo. dat "str x '7c0d0a'" into table deptreplacefields terminated by ', 'optionally enclosed by' "'trailing nullcols (deptno, dname" upper (: dname) ", loc "Upper (: loc)", comments char (1000000) -- sqlldr default input fields are char (255 ). Char (1000000) indicates that up to 1000000 characters can be entered.) select * from dept;

2) Load lob data of the external network.

You need to load data files containing some file names into lob, instead of mixing lob data with structured data. In this way, you do not need to use one of the above four methods to avoid entering data.
Embedded line breaks, which frequently occur in a large amount of text or binary data. Sqlldr calls this additional data file lobfile.
Sqlldr also supports loading structured data files. It can tell sqlldr how to parse lob data from another file, so that it can load a part of it as each row of structured data.
Sqlldr calls this externally Referenced File a complex level-2 data file.

Lobfile data adopts the following format:

Fixed Length Field (loading byte 100 to 10000 from lobfile );
A Bounded field (ending with a character or ending with a character); -- most common, ending with a file terminator (EOF)
Length/value pair, which is a side length field

-- Create table lob_demo (owner varchar2 (255), time_stamp date, filename varchar2 (255), data blob) of the table that loads data -- assume there is a directory, it contains files to be loaded into the database. The following are the owner, time_stamp, file name, and file load data infile * replaceinto table lob_demo (owner position (), time_stamp position (44: 55) date "Mon DD HH24: MI", filename position (57:100), data lobfile (filename) terminated by EOF) begindata-rw-r -- 1 tkyte 1220342 jun 17 classes12.zipselect owner, time_stamp, filename, dbms_lob.getlength (data) from lob_demo;

3) Load lob data to the object Column

It is generally used to load images.

Create table image_load (id number, name varchar2 (255), image ordsys. ordimage) -- first understand the ordsys. ordimage type

The control file for loading such data is as follows:

load datainfile *into table image_loadreplacefields terminated by ','(id,name,file_name filler,image column object( source column object ( localdata lobfile(file_name) terminated by EOF     nullif file_name='none' )))begindata1,icons,icons.gif

Note: column object tells sqlldr that this is not a column name, but a part of the column name.

The column name is image. source. localdata.

select * from image_load

-- Continue to edit the attribute begin for c in (select * from image_load) loop c. image. setproperties; -- setproperties is ordsys. the ordimage type provides a method to process the image itself, and update the object's other attributes with appropriate values: end loop; end;

Additional information:

Use plsql to load lob data

create table demo (id int primary key,theclob clob)create or replace directory dir1 as 'D:\oracle';SQL> host echo 'hello world!' >d:/oracle/test.txtdeclare l_clob clob; l_bfile bfile;begin insert into demo values (1, empty_clob()) returning theclob into l_clob; l_bfile := bfilename('DIR1', 'test.txt'); dbms_lob.fileopen(l_bfile); dbms_lob.loadfromfile(l_clob, l_bfile, dbms_lob.getlength(l_bfile)); dbms_lob.fileclose(l_bfile);end;select dbms_lob.getlength(theclob),theclob from demo;

Note:

By default, the Created directory is in uppercase DIR1. If the directory is written as dir1, an error is prompted. To use a directory name that is mixed with upper and lower cases, a quote identifier should be used to create such a directory, as follows:

create or replace directory "dir2" as 'D:\oracle';

The above content is a small Editor to share with you about how to use SqlLoader, I hope you will like it.

Articles you may be interested in:

• Oracle high-speed Batch Data Loading tool SQL * loader instructions
• [Oracle] common tool set SQL * Loader usage
• SQL LOADER error Summary