Sqlloader How to use _mssql

Sql*loader (SQLLDR) is a high-speed batch data loading tool for Oracle. This is a useful tool for loading data into the Oralce database in a variety of flat file formats. Today I saw the application for the use of *loader, I have a small try, recorded in this

1, suppose to insert the data table ftest, the field is (ID,USERNAME,PASSWORD,SJ)

2, the Import table data in TXT format storage, named Data.txt

 1 F F 2010-8-19
2 F1 F1 2010-8-19
3 f2 f2 2010-8-19
4 f3 f3 2010-8-19
5 f4 f4 2010-8-19

3, write the control file, the format is a CTL, named Cont.ctl content as follows:

 The load data
infile ' c:\data.txt ' inserts into the 
table Ftest
fields terminated by ""
(Id,us ERNAME,PASSWORD,SJ)

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

4 executing in the CMD command window

Sqlldr Fyzh/fyzh Control=c:\cont.ctl Data=c:\data.txt

5 View the table in Plsql ftest

View has been successfully inserted.

Re-learning Sqlldr

One of the simplest examples of SQLLDR import data:

The load Data
infile *-tells SQLLDR to load the information contained in the control file itself
into table dept-to which table
fields terminated by ', ' The data load form should be a comma-delimited value
(deptno,dname,loc)--The column begindata to be loaded-
-to tell the SQLLDR that      the following is loaded into the Dept table Data
10,sales, Virginia
20,accounting,virginia
30,consulting,virginia
40,finance,virginia
CREATE TABLE Dept
(deptno number (2) Constraint DEPT_PK primary key,
dname varchar2 (),
loc
varchar2) Sqlldr userid=gwm/gwm@fgisdb control=c:\demol.ctl
select * from dept;
1  Sales  Virginia
2  Accounting  Virginia
3  Consulting  Virginia
4  Finance  Virginia

Four ways to load SQLLDR imports:

APPEND: The original table has data added to the back
INSERT: Load Empty table if the original table has data Sqlloader will stop the default value
REPLACE: The original table has data the original data will be deleted all
TRUNCATE: The specified content and replace will delete the existing data with the TRUNCATE statement

FAQ for loading data with SQLLDR

1. How to load delimitation data

1 data that is delimited by a particular character, may be enclosed in quotation marks, which is the most common data format for the current flat file.
For bound data, the most commonly used format is comma-delimited value format. With this file format, each field in the data is separated by a comma from the next field. Text strings can be enclosed in quotes so that the string itself contains commas. If the string must also contain quotes, the general convention is to use two quotes. Loading the bounding data, 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 the last part of the control file is modified 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,virginia
select * FROM dept
1  Ten  Sales  Virginia,usa
2  Accounting  Va, "USA"
3  Consulting  Virginia 
4  Finance  Virginia

2 Another commonly used format is the tab-bound data. There are two ways to use the terminated by clause to load this data:

Terminated by X ' 09 '--use a hexadecimal-formatted tab; If you use ASCII, the tabs should be 9

 Terminated by whitespace
--using terminated by whitespace
load data
infile to
table Dept
Replace
fields terminated by whitespace
(Deptno,dname,loc) 
Begindata     
Sales Virginia 
select * from dept;
1  Sales  Virginia-
-using terminated by X '? '
load Data
infile * into
table Dept
replace
fields terminated by X '
(deptno,dname,loc) 
begindata        Sales        Virginia
SELECT * from dept;
1  10  

Sales-Because once a tab is encountered, a value is output.

Therefore, assigning 10 to deptno,dname gets null because there is no data between the first tab and the Second tab

3) Use of SQLLDR filler keywords

such as skipping tabs

Load Data
infile * into
table dept
replace
Fields terminated by X '
(deptno,dummy1 filler, Dname,dummy2 filler,loc) 
begindata        Sales        Virginia
select * from dept;
1  Sales  Virginia

2. How to load fixed format data

To load fixed-position data, you will use the POSITION keyword in the control file.

Load Data
infile * into
table dept
Replace
(deptno position (1:2), dname position
 (3:16),
 Loc Position (17:29)
 ) 
begindata     
10Accounting   virginia,usa
select * from dept;
1  Accounting   Virginia,usa

This control file does not use the terminated by clause, but instead uses position to tell the Sqlldr field where to start and where to 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 file:

Load Data
infile * into
table dept
Replace
(deptno position (1:2),
 dname position (3:16),
 Loc Position (17:29),
 entire_line position (1:29)
 ) 
begindata     
10Accounting   Virginia , USA
SELECT * from dept;
1  Accounting   virginia,usa  10Accounting  
Virginia,usa

When using position, you can use relative offsets, or you can use absolute offsets. The preceding example uses an absolute offset, specifying exactly where the field starts, where it ends, or
The preceding control file is rewritten as follows:

Load Data
infile * into
table dept
Replace
(deptno position (1:2), dname position
 (*:16),
 Loc Position (*:29),
 entire_line position (1:29)
 ) 
begindata     
10Accounting   
Virginia,usa

* Indicates that the control file concludes where the last field ends. Therefore, in this case, (*:16) is the same as (3:16). Note that the control file can be mixed with relative and absolute positions.
Also, when you use the * notation, you can add it to the offset. For example, dname from the end of Deptno, two characters, you can use (*+2:16), which is equivalent to (5:16).

The end position in the position clause must be the absolute column position at the end of the data. Sometimes it can be easier to specify the length of each field, especially if the fields are contiguous. Using this

Way, just tell SQLLDR: The record starts with the first byte, and then specifies the length of each field. As follows:

Load Data
infile * into
table dept
Replace
(deptno position (1) char (2),
 dname position (*) char (14) ,
 loc Position (*) char (
 entire_line position (1) char ()) 
begindata     
10Accounting   Virginia,usa 
SELECT * FROM dept;

3. How to load the date

Use the SQLLDR load date with only the date 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.

If you modify the Dept table as follows:

ALTER TABLE dept add last_updated Date;
Load Data
infile * into
table dept
replace
Fields terminated by ', '
(Deptno,
 dname,
 Loc,
 last_updated date ' dd/mm/yyyy '
 ) 
begindata     
10,accounting,virginia,1/5/2000
SELECT * from dept;
1  
Accounting  
 Virginia   
 2000-5-1

4. How to load data using functions

If you want to make sure that the data that is loaded is uppercase, you can overwrite the control file as follows:

Load Data
infile * into
table dept
replace
Fields terminated by ', '
(deptno,
 dname "Upper" (: Dname) ",
 Loc" Upper (: Loc) ", last_updated
 date ' dd/mm/yyyy '
 ) 
begindata 10,accounting     
, virginia,1/5/2000
SELECT * from dept;
1  
 ACCOUNTING  
 VIRGINIA    
2000-5-1

The following control file loading data cannot be imported

Load Data
infile * into
table dept
replace
Fields terminated by ', '
(deptno,
 dname " Upper (:d name) ",
 Loc" Upper (: Loc) ",
 last_updated date ' dd/mm/yyyy ',
 entire_line ':d eptno| |:d name| |: loc| |:last_updated "
 ) 
begindata     
10,accounting,virginia,1/5/2000

1) The use of trailing nullcols: The general default for good

The solution is to use trailing nullcols. Thus, if the data in a column does not exist in the input record, SQLLDR binds a null value to the column.

In this case, increasing the trailing nullcols results in the binding variable: Entire_line becomes null.

Load Data
infile * into
table dept
replace
Fields terminated by ', '
trailing nullcols
(DEPTNO,
 dname "Upper (:d name)",
 Loc "Upper (: Loc)",
 last_updated date ' dd/mm/yyyy ',
 entire_line ':d eptno| |: dname| |:loc| |:last_updated "
 ) 
begindata     
10,accounting,virginia,1/5/2000
select * from dept;
1  ACCOUNTING  VIRGINIA  10accountingvirginia1/5/2000  2000-5-1

2 The use of case in Sqlldr

Suppose the input file has a date in the following format:
HH24:MI:SS: only one time; date time defaults to Sysdate
DD/MM/YYYY: Only one date, the time defaults to Midnight 0 o'clock
HH24:MI:SS dd/mm/yyyy: Both date and time are explicitly provided

The following control files are available

Load Data
infile * into
table dept
replace
Fields terminated by ', '
trailing nullcols
(DEPTNO,
 dname "Upper (:d name)",
 Loc "Upper (: Loc)",
 last_updated
 "Case 
 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
 "
 )
 Begindata
10,sales,virginia,12:03:03 17/10/2005
20,accounting,virginia,02:23:54
30,Consulting, virginia,01:24:00 21/10/2006
40,finance,virginia,17/8/2005
alter session set Nls_date_format= ' Dd-mon-yyyy Hh24:mi:ss ';
SELECT * FROM dept;

5. How to load data with inline line breaks

1 use other characters that are not newline characters to represent line breaks, and use an SQL function to replace the text with a CHR (10) at load time.

ALTER TABLE Dept Add comments VARCHAR2 (4000);
--Use the following to load the download
data
infile * into
table dept
replace
Fields terminated by ', '
trailing Nullcols
(deptno,
 dname "Upper (:d name)",
 Loc "Upper (: Loc)",
 Comments "Replace" (: Comments, ' \\n ', CHR) "--' \\n ' newline character replaced by Chr (10)
Begindata 10,sales,virginia,this is the sales\noffice in
Virginia

Note: The call must use the \\n to represent the substitution, not \ nthe

2 Use the Fix property on the infile instruction to load a fixed-length flat file.
With this method, the input data must appear in a fixed-length record. For fixed-position data, it is particularly appropriate to use the Fix property, which is generally a fixed-length file.
When you use this method, the data must be stored externally and cannot be stored in the control file itself.

--Control file
load data
infile demo.dat "Fix 80"--Specifies the input data file Demo.dat, 80 bytes into
Table Dept Replace per record in this file
fields terminated by ', '
trailing nullcols
(deptno,
 dname "Upper (:d name)",
 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 line end tag is \ n-chr (10), and the line end tag for the Windows NT platform is \ r \ n, chr (13) | | CHR (10);
You can use the Trim built-in SQL function in the control file to complete the truncated trailing whitespace

SELECT * FROM dept;

3 in the infile instruction, using the var attribute, load a widening file, in the format used by the file, the first few bytes of each row specify the length of this line

--Control file
load data
infile Demo.dat "var 3"--indicates that the first three bytes are used to record the number of bytes per row into
Table dept
replace
Fields Terminated by ', '
trailing nullcols
(deptno,
 dname "Upper (:d name)",
 Loc "Upper (: Loc)",
 Comments
)
--Data file
05410,sales,virginia,this is the Sales office in Virginia

Note: line breaks on Unix are counted as one byte and two bytes in Windows NT

SELECT * FROM dept;

4 using the str attribute on the infile instruction, load a widening file with a sequence of characters to represent the line terminator instead of a newline character
The str attribute is specified in hexadecimal, and the easiest way to get a hexadecimal string is to use SQL and Utl_raw to generate the hexadecimal string. As in the UNIX platform, the line end tag is Chr (10), and our special character is a pipe symbol (|), which can be written as:

 Select Utl_raw.cast_to_raw (' | ' | | Chr ()) from dual;--visible on UNIX for x ' 7c0a '

Use on Windows

 Select Utl_raw.cast_to_raw (' | ' | | Chr (13) | | Chr from dual;--to X ' 7c0d0a '
--control file
load data
infile demo.dat "str x ' 7c0d0a '" into 
table dept
Replace
fields terminated by ', '
trailing nullcols
(deptno,
 dname ' Upper (:d name),
 Loc "Upper" (: LOC)
 Comments
--Data file 10,sales,virginia,this is the
Sales
office in virginia|
SELECT * FROM dept;

6. Load LOB Data

1) to load inline LOB data. These LOB data usually have line breaks and other special characters embedded in them

--Modify Table Dept truncate tables Dept;
ALTER TABLE Dept Drop Column Comments;
ALTER TABLE dept Add comments Clob;
--Data file 10,sales,virginia,this is the Sales office in virginia|
20,accounting,virginia,this is the Accounting office in virginia|
30,consuling,virginia,this is the consuling office in virginia| 40,finance,virginia, "This are the Finance office in Virginia,it has embedded commas and are much longer than the other Comme NTS filed. If you feel the need to add double quotes text as this: "' You'll need to double up those quotes! '" To preserve them in the string. This field keeps going for up to 1000000 bytes (because of the control file definition I used) or until we hit the magic a nd of record marker, the |
Followed by a "line-it is Right" |-> | --Control file load data infile demo.dat "str x ' 7c0d0a '" Into Table Dept replace fields terminated by ', ' optionally enclosed by ' "' Trailing Nullcols (Deptno, Dname" Upper (:d name) ", loc" Upper (: Loc) ", comments char (1000000)--sQlldr fields that are entered by default are char (255). char (1000000) is allowed to enter up to 1 million characters) select * from dept;

2) load the external LOB data.

You need to load data files with some file names into the lob, rather than having LOB data mixed with structured data. This eliminates the need to use one of the 4 methods above to circumvent the input data
Inline newline character problem, which occurs frequently in a large number of text or binary data. Sqlldr called this extra data file a lobfile.
Sqlldr can also support the loading of structured data files. You can tell Sqlldr how to parse LOB data from another file so that you can load a portion of it as each row in the structured data.
Sqlldr says this externally referenced file is a complex level two data file.

Lobfile data takes one of the following formats:

Fixed-length fields (bytes from Lobfile 100 to 10000);
A bounding field (ending with a character or Fu Guachi with a word);-most commonly, ending with a file Terminator (EOF)
Length/value pairs, which is a side-length field

--the table that loads the data
create TABLE Lob_demo
(owner VARCHAR2 (255),
time_stamp date,
filename varchar2 (255),
Data blob)
--Suppose you have a directory that contains files that you want to load into the database. The following is the owner,time_stamp that you want to load the file, the filename and the file itself
load data 
infile *
replace into
table Lob_demo
(owner Position (17:25),
 time_stamp position (44:55) Date "Mon DD hh24:mi",
filename position (57:100),
data Lobfile (filename) terminated by EOF
)
begindata
-rw-r--r--1 tkyte tkyte 1220342 June 15:26 Classes12.zi P
Select Owner,time_stamp,filename,dbms_lob.getlength (data) from Lob_demo;

3 loading LOB data into an object column

Generally used to load images

 CREATE TABLE image_load (
 ID number,
 name VARCHAR2 (255),
 image ordsys.ordimage)-- First you need to understand the Ordsys.ordimage type

The control file that loads this data looks like this:

Load Data
infile * into
table image_load
replace
fields terminated by ', '
(ID,
name,
file _name filler,
image Column Object
(
 source Column Object
 (
 localdata lobfile (file_name) Terminated by EOF
     nullif file_name= ' None '))
begindata
1,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 used is Image.source.localdata

SELECT * FROM Image_load

--Continue editing properties of loaded incoming data
begin for
 C in (SELECT * from Image_load) loop
  c.image.setproperties;-- SetProperties is the method provided by the Ordsys.ordimage type, processing the image itself and updating the rest of the object's property end loop with the appropriate values
 ;
End

Additional Introduction:

Using 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.txt
declare
 l_clob CLOB;
 L_bfile bfile;
Begin
 inserts into the 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:

The directory you create defaults to uppercase DIR1, and if the directory is written as Dir1, you will be prompted for an error, and if you want to use a mixed case directory name, you should have quoted identifiers when you create such a directory, as follows:

Create or replace directory "Dir2" as ' D:\oracle ';

The above content is small to share with you about how sqlloader use of relevant information, I hope you like.