Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to use SqlLoader. The editor thinks it is very practical, so I share it with you as a reference. I hope you can get something after reading this article.

SQL*Loader (SQLLDR) is a high-speed batch data loading tool for Oracle. This is a very useful tool for loading data into Oralce databases in a variety of flat file formats. Today, I read the application for the use of * loader. I tried it a little bit and recorded it here.

1. Suppose the table ftest in which the data is to be inserted, and the field is (id,username,password,sj)

2. The data of the import table is stored in txt format, named 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 the control file in the format of ctl and named cont.ctl 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, use insert, if there is data, use append, delete the old data and insert the new data with replace or truncate

4 execute 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 it has been inserted successfully.

Relearn sqlldr

One of the simplest examples of sqlldr importing data:

Load datainfile *-- tells sqlldr that the data to be loaded is included in the control file itself into table dept-- which table fields terminated by','--the data loading form should be a comma-delimited value (deptno,dname,loc)-- the column to be loaded is begindata-- tells the sqlldr that the data to be loaded into the dept table in the following line market: 10 ~ (th) Magnum _ Virginia 20 ~ (th) Accounting10 ~ (th) Virginia 30 ~ (th) Consulting, Virginia 40 ~ (th) 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

There are four loading methods for sqlldr import:

APPEND: add data to the original table INSERT: load the empty table if the original table has data sqlloader will stop the default value REPLACE: the original table has data, the original data will all be deleted TRUNCATE: the content specified is the same as replace will delete the existing data using the truncate statement

Load the FAQ of data with SQLLDR

1. How to load delimited data

1) delimited data, that is, data separated by a special character, may be enclosed in quotation marks, which is the most common data format in current flat files. For delimited data, the most common format is the comma-separated value format. In this file format, each field in the data is separated from the next field by a comma. The text string can be enclosed in quotation marks so that the string itself contains a comma. If the string must also contain quotation marks, the general convention is to use two quotation marks. Load delimited data, and the corresponding typical control file is similar to the previous example, but 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, and each field can be enclosed in double quotes. 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 Consulting Virginia," Va, "" USA "" 30 Finance Virginia ConsultingFinance40 Accounting Va * from dept1 10 Consulting Virginia 20 Accounting Va, "USA" 3 30 Consulting Virginia 4 40 Finance Virginia

2) another commonly used format is tab delimited data. There are two ways to load this data using the terminated by clause:

Terminated by Xtab 09'-use tabs in hexadecimal format; if ASCII is used, tabs should be 9

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

Sales-- because once a tab is encountered, it will output a value.

Therefore, assigning 10 to deptno,dname results in null because there is no data between the first tab and the second tab

3) use the filler keyword of sqlldr

Such as skipping tabs

Load datainfile * into table deptreplacefields terminated by Xero09' (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-width fixed position data, the position keyword will be 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 10Accounting Virginia,USA

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

Alter table dept add entire_line varchar (29)

And use the following control files:

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 10Accounting Virginia,USA 10Accounting Virginia,USA

When using position, you can use either a relative offset or an absolute offset. The previous example uses an absolute offset to specify where the field starts and ends, or you can rewrite the previous control file 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

* instructs the control file to find out where the previous field ends. Therefore, in this case, (*: 16) is the same as (3:16). Note that control files can use a mixture of relative and absolute positions. In addition, when using the * notation, you can add it to the offset. For example, dname after the end of deptno; two characters starting, you can use (* + 2:16), that is, equivalent to (5:16).

The end position in the position clause must be the absolute column position where the data ends. Sometimes it may be easier to specify the length of each field, especially if they are contiguous. Adopt this kind of

Simply tell sqlldr that the record starts with the first byte and then specifies 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 date

To load a date using sqlldr, simply date the data type in the control file and specify the date mask to use. This date mask is the same as the date mask used in to_char and to_date in the database.

For example, 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 from dept;1 10 Accounting Virginia 2000-5-1

4. How to use functions to load data

If you want to make sure that the loaded data is uppercase, you can overwrite 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 from dept;1 10 ACCOUNTING VIRGINIA 2000 select * from dept;1 10 ACCOUNTING VIRGINIA 2000-5-1

The following control file loading data cannot be imported

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 10pm

1) the use of TRAILING NULLCOLS: it is generally good by default.

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

In this case, adding TRAILING NULLCOLS causes the binding variable: entire_line to become 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 AccountingMagnum Virginia 1pact 2000 select * from dept;1 10 ACCOUNTING VIRGINIA 10AccountingVirginia1/5/2000 2000-5-1

2) the use of case in sqlldr

Suppose there is a date in the following format in the input file: HH24:MI:SS: there is only one time; the date time defaults to sysdateDD/MM/YYYY: there is only one date, and the time defaults to 0 midnight HH24:MI:SS DD/MM/YYYY: both dates and times are provided explicitly

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,'hh34:mi:ss dd/mm/yyyy') when instr (: last_updated,':') > 0 then to_date (: last_updated,'hh34: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 hh34:mi:ss' Select * from dept

5. How to add data with line breaks embedded in it

1) represent the newline character with other characters that are not newline characters, and replace the text with a CHR (10) with a sql function when loading.

Alter table dept add comments varchar2 (4000);-- use the following to load the text load datainfile * into table deptreplacefields terminated by', 'trailing nullcols (deptno, dname "upper (: dname)", loc "upper (: loc)", comments "replace (: comments,'\ nChr (10))"-- the newline character chr (10) instead of) begindata10,Sales,Virginia,this is the sales\ noffice in Virginia

Note:\ n must be used in the call to indicate the replacement character, not\ n

2) use the FIX attribute on the infile instruction to load a flat file of fixed length. Using this method, the input data must appear in the fixed-length record. For fixed-location data, the FIX attribute is particularly appropriate, and these files are generally fixed-length files. In addition, when using this method, the data must be stored externally, not in the control file itself.

-- Control file load datainfile demo.dat "fix 80"-- specifies the input data file demo.dat, where 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 Magi SalesJournal this is the sales\ noffice in Virginia 20, this is the sales\ noffice in Virginia.

Note:

On unix, the line end tag is\ ni.e. CHR (10), while the line end tag of windows nt platform is\ r\ ni.e. CHR (13) | | CHR (10); you can use the trim built-in sql function in the control file to truncate the trailing blank character.

Select * from dept

3) use the VAR attribute on the infile instruction to load a widening file in the format in which the first few bytes of each line specify the length of the line

-- Control file load datainfile demo.dat "var 3"-- indicates that the first three bytes are used to record the number of bytes per line into table deptreplacefields terminated by', 'trailing nullcols (deptno, dname "upper (: dname)", loc "upper (: loc)", comments)-- data file 05410.

Note: the newline character is only one byte on unix and two bytes on windows nt

Select * from dept

4) use the STR attribute on the infile instruction to load a broadened file in which the line Terminator is represented by a sequence of characters instead of a newline character that the STR attribute is specified in hexadecimal. The easiest way to get a hexadecimal string is to use sql and utl_raw to generate a hexadecimal string. For example, on the unix platform, the line end tag is CHR (10), and our special character is a pipe symbol (|), then it can be written as follows:

Select utl_raw.cast_to_raw ('|'| | chr (10)) from dual;-- is visible on unix as xroom7C0A'

Use on windows

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

6. Load lob data

1) load inline lob data. These lob data are usually embedded with newline characters and other special characters.

-- modify the table depttruncate table dept;alter table dept drop column comments;alter table dept add comments clob;-- data file 10, this is the Financeoffice in Virginia,it has embedded commas and ismuch longer than the other 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," | 20, is the Accountingoffice in Virginia, consult, and consult, "this is the Financeoffice in Virginia,it has embedded commas and ismuch longer than the other 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 to1000000 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 xylene 7C0D0A'"into table deptreplacefields terminated by", 'optionally enclosed by' "'trailing nullcols (deptno, dname" upper (: dname) ", loc" upper (: loc) ", comments char (1000000)-the default input fields for sqlldr are char. Char (1000000) means up to 1000000 characters are allowed to be entered) select * from dept

2) load lob data for outreach.

Instead of mixing lob data with structured data, you need to load data files that contain some filenames into lob. This eliminates the need to use one of the four methods mentioned above to avoid the problem of line breaks in input data, which occurs frequently in large amounts of text or binary data. Sqlldr calls this extra data file lobfile. Sqlldr can also support loading structured data files. You can tell sqlldr how to parse lob data from another file so that part of it can be loaded as each row in the structured data. Sqlldr calls this externally referenced file a complex secondary data file.

Lobfile data is in one of the following formats:

Fixed-length fields (from lobfile load bytes 100 to 10000); delimited fields (ending with or enclosing a character);-- most often, ending with a file Terminator (EOF) length / value pair, which is an edge length field

-- Table create table lob_demo (owner varchar2, time_stamp date,filename varchar2, data blob) that loads the data-- suppose you have a directory that contains the files you want to load into the database. The following is the owner,time_stamp that you want to load the file, the file name and the file itself load data infile * replaceinto table lob_demo (owner position (17:25), time_stamp position (44:55) date "Mon DD HH24:MI", filename position (57replaceinto table lob_demo 100), data lobfile (filename) terminated by EOF) begindata-rw-r--r-- 1 tkyte tkyte 1220342 jun 17 15:26 classes12.zipselect owner,time_stamp,filename,dbms_lob.getlength (data) from lob_demo

3) load lob data into object columns

Commonly used to load images

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

The control file that loads this 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 part of it.

The column name used is image.source.localdata

Select * from image_load-- continues to edit the loaded data properties begin for c in (select * from image_load) loop c.image.setpropertiesMattel setproperties, a method provided by the ordsys.ordimage type to process the image itself and update the remaining properties of the object end loop;end with appropriate values

Additional introduction:

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 worldview' > d:/oracle/test.txtdeclare l_clob clob; l_bfile bfile;begin insert into demo values (1, empty_clob ()) returning theclob into lumped 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:

The created directory defaults to uppercase DIR1. If the directory is written as dir1, an error will be prompted. If you want to use a directory name mixed with case, you should create such a directory with an identifier in quotation marks, as shown below:

Create or replace directory "dir2" as'D:\ oracle'; this article on "how to use SqlLoader" ends here. I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.