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

This article introduces the relevant knowledge of "how to use SQL Loader". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Oracle's SQL*LOADER can load external data into database tables. Here are the basic features of SQL*LOADER:

1) data that can be loaded into different data types and multiple data files

2) data can be loaded in fixed format, free delimiting and indexable long format

3) can load binary and compress decimal data

4) data can be loaded on multiple tables at a time

5) connect multiple physical records into one record

6) decompose a single record and load it into the table

7) A unique KEY can be generated with a number of pairs of defined columns.

8) disk or tape data files can be loaded into tabulation

9) provide load error report

10) the integer string in the file can be automatically converted into compressed decimal and loaded into the list.

1.2 Control file

The control file is a text file written in a language that can be recognized by SQL*LOADER. SQL*LOADER can find the data that needs to be loaded according to the control file. And analyze and interpret the data. The control file consists of three parts:

Global options, rows, number of skipped records, etc.

Input data specified in the l INFILE clause

L data property description.

1.3 input Fil

For SQL*Loader, the input data is in addition to the control file. SQL*Loader can read data from one or more specified files. If the data is specified in the control file, it should be written in INFILE * format in the control file. Use INFILE "fix n" when the data is in a fixed format (the same length) and is obtained in a file

Load data

Infile 'example.dat' "fix 11"

Into table example

Fields terminated by', 'optionally enclosed by' "'

(col1 char (5)

Col2 char (7))

Example.dat:

001, cd, 0002,fghi

00003,lmn

1, "pqrs"

0005,uvwx

Use INFILE "var n" when the data is in a variable format (with different lengths) and is obtained in a file. Such as:

Load data

Infile 'example.dat' "var 3"

Into table example

Fields terminated by', 'optionally enclosed by' "'

(col1 char (5)

Col2 char (7))

Example.dat:

009hello,cd,010world,im

012my,name is

1.4 Bad file

Bad files contain records that are rejected by SQL*Loader. Rejected records may be records that do not meet the requirements.

The name of the bad file is given by the BADFILE parameter of the SQL*Loader command.

1.5 days log files and log information

When SQL*Loader starts execution, it automatically creates log files. The log file contains a summary of the load, error messages in the load, and so on.

Control file syntax

The format of the control file is as follows:

OPTIONS ({[SKIP=integer] [LOAD = integer])

[ERRORS = integer] [ROWS=integer]

[BINDSIZE=integer] [SILENT= (ALL | FEEDBACK | ERROR | DISCARD)])

LOAD [DATA]

[{INFILE | INDDN} {file | *}

[STREAM | RECORD | FIXED length [BLOCKSIZE size] |

VARIABLE [length]]

[{BADFILE | BADDN} file]

{DISCARDS | DISCARDMAX} integr]

[{INDDN | INFILE}. . . ]

[APPEND | REPLACE | INSERT]

[RECLENT integer]

[{CONCATENATE integer |

CONTINUEIF {[THIS | NEXT] (start [: end]) LAST}

Operator {'string' | X' hex'}}]

INTO TABLE [user.] table

[APPEND | REPLACE | INSERT]

[WHEN condition [AND condition]...]

[FIELDS [delimiter]]

(

Column {

RECNUM | CONSTANT value |

SEQUENCE ({integer | MAX | COUNT} [, increment]) |

[POSITION ({start [end] | * [+ integer]})

)]

Datatype

[TERMINATED [BY] {WHITESPACE | [X] 'character'}]

[[OPTIONALLY] ENCLOSE [BY] [X] 'charcter']

[NULLIF condition]

[DEFAULTIF condotion]

}

[,...]

)

[INTO TABLE...]

[BEGINDATA]

1) data file to be loaded:

1.INFILE and INDDN are synonyms, followed by data files to be loaded. If you use *, it means that the data is in the control file. You can follow several files after INFILE.

2.STRAM means to read data one byte at a time. The new line represents the new physical record (the logical record can consist of several physical records).

3.RECORD uses the host operating system file and record management system. Use this method if the data is in the control file.

3. The length of the record to be read by FIXED length is length bytes

4. VARIABLE the length contained in the first two bytes of the record being read, and the possible length of the length record. The defect is 8k bytes.

5. BADFILE is synonymous with BADDN. Oracle cannot load data into those records of the database.

6. DISCARDFILE and DISCARDDN are synonyms. Record failed data.

7. DISCARDS and DISCARDMAX are synonyms. Integer is the maximum number of files discarded.

2) loading method:

1.APPEND adds rows to the table.

2.INSERT adds rows to the empty table (exits if there are records in the table).

3.REPLACE empties the table before loading the data.

4. RECLEN is used in two situations: 1) SQLLDR cannot automatically calculate the record length, 2) or when the user wants to read the complete record of the file. For the latter, Oracle can only write bad records in the wrong place as usual. If you look at the whole record, you can write the whole record to a bad file.

3) specify the maximum record length:

1. CONCATENATE allows the user to set an integer that represents the number of logical records to be combined.

4) establish a logical record:

1.THIS checks the current record condition and, if true, connects the next record.

2.NEXT checks the next record condition. If true, connect the next record to the current record.

2. Start: end indicates that you want to check whether there is a column in the THIS or NEXT string to continue the string to determine whether to connect. For example, continueif next (1-3) = 'WAG' or continueif next (1-3) = Xroom0d03if'

5) specify the table to load:

The name of the table to be added by 1.INTO TABLE.

2.WHEN is similar to select WHERE. Used to check the record, for example: when (3-5) = 'SSM' and (22) =' * "

6) introduce and include the fields in the record:

1. FIELDS gives the delimiter of the fields in the record. The FIELDS format is as follows:

FIELDS [TERMIALED [BY] {WHITESPACE | [X] 'charcter'}]

[[OPTIONALLY] ENCLOSE [BY] [X] 'charcter']

TERMINATED reads the previous field and starts reading the next field until it is introduced.

WHITESPACE means that the Terminator is a space. Includes spaces, Tab, line feeds, page feeds and carriage returns. If you want to judge but characters, you can enclose them in single quotation marks, such as Xquote 1B', etc.

OPTIONALLY ENCLOSED indicates that the data should be enclosed in special characters. It can also be enclosed in TERMINATED characters. To use OPTIONALLY, use TERMINLATED at the same time.

ENCLOSED refers to the data within the two delimiters. If both ENCLOSED and TERMINAED are used, their order determines the order in which they are calculated.

7) define columns:

Column is the list name. The value of the column can be:

BECHUM represents the number of logical records. The first record is 1 and the second record is 2.

CONSTANT means to assign a constant.

SEQUENCE indicates that the sequence can start with any sequence number in the format:

SEQUENCE ({integer | MAX | COUNT} [, increment]

POSITION gives the location of the column in the logical record. Can be absolute or relative to the value of the previous column. The format is:

POSITION ({start [end] | * [+ integer]})

Start start position

* indicates that it starts immediately after the previous field.

+ the number of positions from the front column to the back bar.

8) define the data type:

You can define 14 data types:

CHAR

DATE

DECIMAL EXTERNAL

DECIMAL

DOUBLE

FLOAT

FLOAT EXTERNAL

GRAPHIC EXTERNAL

INTEGER

INTEGER EXTERNAL

SMALLINT

VARCHAR

VARGRAPHIC

1. Character type data

CHAR [(length)] [delimiter]

Length defaults to 1. 0.

two。 Date type data

DATE [(length)] ['date_format' [delimiter]

Use the to_date function to limit.

3. Decimal system in character format

DECIMAL EXTERNAL [(length)] [delimiter]

A decimal number used for regular format (not binary = > one bit equals one bit).

4. Compress data in decimal format

DECIMAL (digtial [, divcision])

5. Double precision symbol point binary

DOUBLE

6. Common symbol binary

FLOAT

7. Number of character format points

FLOAT EXTERNAL [(length)] [delimiter]

8. Double-byte string data

GRAPHIC [(legth)]

9. Double-byte string data

GRAPHIC EXTERNAL [(legth)]

10. Regular full-word binary integers

INTEGER

11. Character format integer

INTEGER EXTERNAL

12. Conventional full-word binary data

SMALLINT

13. Variable length string

VARCHAR

14. Variable double-byte string data

VARGRAPHIC

2.2 write control file CTL

1. File name of each data file

2. Each data file format

3. Attributes of each data record field in each data file

4. Properties of ORACLE columns that accept data

5. Data definition

6. Other

Requirements for data files:

Specification of data types

CHAR character type

INTEGER EXTERNAL integer

DECIMAL EXTERNAL floating point type

3.1 contents of the data file

Can be a file under OS; or specific data that can be followed under a control file. The data file can be:

1. Binary and character format: LOADER can read binary files (as characters) in the list.

2. Fixed format: the data, data type and data length in the record are fixed.

3. Variable format: each record has at least one variable length data field, and a record can be a continuous string.

The demarcation of a data segment (such as name, age), such as "," as the division of a field; "," as data

Brackets, etc.

4. LOADER can use physical records of multiple consecutive fields to form a logical record to record the operation of the file, including the following:

1. Run date: software version number

2. All input and output file names; display information and supplementary information on the command line

3. Report for each loading information: such as table name, loading status; for initial loading, add truncation or update

Enter the selection, column information

4. Data error report: error code; abandoned record report

5. Each load X report: load lines; load lines, which may be skipped; rows that may be rejected; may put

Number of abandoned rows, etc.

6. Statistical summary: use space (package size, length); read records, load records, skip notes

The number of records; the number of rejected records, the number of abandoned records; running time, etc.

=

A little summary of sql load

Sqlldr userid=lgone/tiger control=a.ctl

LOAD DATA

INFILE 't.dat' / / File to import

/ / INFILE 'tt.date' / / Import multiple files

/ / INFILE * / / the content to be imported is just below the BEGINDATA in the control file followed by the imported content

INTO TABLE table_name / / specify the loaded table

BADFILE'c:\ bad.txt' / / specify a bad file address

* here are four ways to load tables

APPEND / / the data in the original table is added at the end.

/ / INSERT / / load an empty table if the original table has data sqlloader will stop the default value

/ / REPLACE / / the previous table has data and the previous data will be deleted

/ / TRUNCATE / / the same content specified as replace will delete existing data with the truncate statement

* the specified TERMINATED can be at the beginning of the table or in the internal fields section of the table

FIELDS TERMINATED BY', 'OPTIONALLY ENCLOSED BY' "'

/ / load this kind of data: 10 lg LG, "lg", "lg,lg"

/ / result in the table: 10 lg "lg" lg,lg

/ / TERMINATED BY X '09' / / in hexadecimal format' 09'

/ / TERMINATED BY WRITESPACE / / load this data: 10 lg lg

Null is allowed if the field of the TRAILING NULLCOLS * table has no corresponding value.

* the following are the fields of the table

(

Col_1, col_2, col_filler FILLER / / FILLER keywords the values of this column will not be loaded

/ / for example: lg,lg,not result lg lg

)

/ / when FIELDS TERMINATED BY','is not declared

/ / (

/ / col_1 [interger external] TERMINATED BY','

/ / col_2 [date "dd-mon-yyy"] TERMINATED BY','

/ / col_3 [char] TERMINATED BY', 'OPTIONALLY ENCLOSED BY' lg'

/ /)

/ / when FIELDS TERMINATED BY','is not declared, use the location to tell the field to load the data

/ / (

/ / col_1 position (1:2)

/ / col_2 position (3:10)

/ / col_3 position (*: 16), / / the start of this field is at the end of the previous field

/ / col_4 position (1:16)

/ / col_5 position (3:10) char (8) / / specify the type of field

/ /)

BEGINDATA / / corresponds to the initial INFILE * the content to be imported is in the control file

10,Sql,what

20,lg,show

=

/ Note that the values after begindata cannot be preceded by spaces

1 * ordinary loading

LOAD DATA

INFILE *

INTO TABLE DEPT

REPLACE

FIELDS TERMINATED BY', 'OPTIONALLY ENCLOSED BY' "'

(DEPTNO

DNAME

LOC

)

BEGINDATA

10 USA sales, "

20 Accounting, "Virginia,USA"

30,Consulting,Virginia

40,Finance,Virginia

50, "Finance", "", Virginia / / loc column will be empty

60, "Finance", Virginia / / loc column will be empty

2 * the situation of FIELDS TERMINATED BY WHITESPACE and FIELDS TERMINATED BY x09'

LOAD DATA

INFILE *

INTO TABLE DEPT

REPLACE

FIELDS TERMINATED BY WHITESPACE

-- FIELDS TERMINATED BY Xbox 09'

(DEPTNO

DNAME

LOC

)

BEGINDATA

10 Sales Virginia

3 * specify which column is not loaded

LOAD DATA

INFILE *

INTO TABLE DEPT

REPLACE

FIELDS TERMINATED BY', 'OPTIONALLY ENCLOSED BY' "'

(DEPTNO

FILLER_1 FILLER, / / the following "Something Not To Be Loaded" will not be loaded

DNAME

LOC

)

BEGINDATA

20 something Not To Be Loaded,Accounting, "Virginia,USA"

4 * column of position

LOAD DATA

INFILE *

INTO TABLE DEPT

REPLACE

(DEPTNO position (1:2)

DNAME position (*: 16), / / the start of this field is at the end of the previous field

LOC position (*: 29)

ENTIRE_LINE position (1:29)

)

BEGINDATA

10Accounting Virginia,USA

5 * use of TRAILING NULLCOLS as an expression of the date of the function

LOAD DATA

INFILE *

INTO TABLE DEPT

REPLACE

FIELDS TERMINATED BY','

TRAILING NULLCOLS / / in fact, the following ENTIRE_LINE has no direct correspondence in the data behind BEGINDATA.

If the first row of the column value of / / is changed to 10, sales, Virginia, 1, 5, and 2000, TRAILING NULLCOLS will not be used.

(DEPTNO

DNAME "upper (: dname)", / / use functions

LOC "upper (: loc)"

One expression of LAST_UPDATED date 'dd/mm/yyyy', / / date also includes' dd-mon-yyyy' 'and so on.

ENTIRE_LINE ": deptno | |: dname | |: loc | |: last_updated"

)

BEGINDATA

10,Sales,Virginia,1/5/2000

20,Accounting,Virginia,21/6/1999

30,Consulting,Virginia,5/1/2000

40,Finance,Virginia,15/3/2001

6 * time problem solved by using custom functions / /

Create or replace

Function my_to_date (p_string in varchar2) return date

As

Type fmtArray is table of varchar2 (25)

L_fmts fmtArray: = fmtArray ('dd-mon-yyyy',' dd-month-yyyy'

'dd/mm/yyyy'

'dd/mm/yyyy hh34:mi:ss')

L_return date

Begin

For i in 1.. L_fmts.count

Loop

Begin

L_return: = to_date (p_string, l_fmts (I))

Exception

When others then null

End

EXIT when l_return is not null

End loop

If (l_return is null)

Then

L_return: =

New_time (to_date ('01011970)) + 1On24Accord60Accord 60 *

P_string, 'GMT',' EST')

End if

Return l_return

End

/

LOAD DATA

INFILE *

INTO TABLE DEPT

REPLACE

FIELDS TERMINATED BY','

TRAILING NULLCOLS

(DEPTNO

DNAME "upper (: dname)"

LOC "upper (: loc)"

LAST_UPDATED "my_to_date (: last_updated)" / / use custom functions

)

BEGINDATA

10pr Salesreel Virginia pr 01Mutual aprilwi 2001

20,Accounting,Virginia,13/04/2001

30,Consulting,Virginia,14/04/2001 12:02:02

40,Finance,Virginia,987268297

50recoverable VirginiaJi 02muraprwi 2001

60,Finance,Virginia,Not a date

7 * merge multiple rows of records into one row

LOAD DATA

INFILE *

Concatenate 3 / / treat several rows of records as one-line records through the keyword concatenate

INTO TABLE DEPT

Replace

FIELDS TERMINATED BY','

(DEPTNO

DNAME "upper (: dname)"

LOC "upper (: loc)"

LAST_UPDATED date 'dd/mm/yyyy'

)

BEGINDATA

10 sales, / / in fact, these three lines are regarded as a line 10, sales, Virginia, 1, 5, and 2000.

Virginia

1/5/2000

/ / this column is also fine with continueif list= ","

Tell sqlldr to find a comma at the end of each line and append the next line to the previous line.

LOAD DATA

INFILE *

Continueif this (1:1) ='-'/ / find out if there is a concatenation character at the beginning of each line-if so, connect the next line as a line

/ / such as-10 Magi SalesMagol Virginia

/ / 1Accord 5Compact 2000 is a line of 10 pencils, Sales1, Virginia, 1, 5, 2, 000.

/ / 1:1 indicates that there is a continueif next starting from the first line and ending at the first line, but continueif list is ideal

INTO TABLE DEPT

Replace

FIELDS TERMINATED BY','

(DEPTNO

DNAME "upper (: dname)"

LOC "upper (: loc)"

LAST_UPDATED date 'dd/mm/yyyy'

)

BEGINDATA / / but it doesn't seem to work like the one on the right

-10 ~ (th) century Salesjia,-10 ~ (th) ~ (th) Sales.Virginia

1/5/2000 1/5/2000

-40,40FinancePhillips Virginia pencils 13Universe 04ax 2001

Finance,Virginia,13/04/2001

8 * load the line number of each line

Load data

Infile *

Into table t

Replace

(seqno RECNUM / / load the line number of each line

Text Position (1 1024))

BEGINDATA

Fsdfasj / / automatically assigns a line number to the seqno field loaded in table t this behavior 1

Fasdjfasdfl / / this behavior 2.

9 * load data with newline characters

Note: unix is different from windows & / n

< 1 >

Use a character that is not a newline

LOAD DATA

INFILE *

INTO TABLE DEPT

REPLACE

FIELDS TERMINATED BY','

TRAILING NULLCOLS

(DEPTNO

DNAME "upper (: dname)"

LOC "upper (: loc)"

LAST_UPDATED "my_to_date (: last_updated)"

The use of COMMENTS "replace (: comments,'\ nnewline character Chr (10))" / / replace helps convert line breaks

)

BEGINDATA

10 is the Sales is the Sales Virginia01Sumi aprillic 2001 this Personality\ Magi

20 is the Accounting AccountingVirginia 13 Gramps 04Accord 2001 this AccountingVirginia13 Grammer 04pm 2001 this AccountingVirginia\ nOffice in Virginia

30 is the Consulting Consulting Virginia 14 Gramps 04 racing 2001 12 Frances 02 purl 02 this is the Consulting\ nOffice in Virginia

40 is the Finance FinanceVirginia 98 7268297 Magneol this month\ nOffice in Virginia

< 2 >

Use the fix property

LOAD DATA

INFILE demo17.dat "fix 101"

INTO TABLE DEPT

REPLACE

FIELDS TERMINATED BY','

TRAILING NULLCOLS

(DEPTNO

DNAME "upper (: dname)"

LOC "upper (: loc)"

LAST_UPDATED "my_to_date (: last_updated)"

COMMENTS

)

Demo17.dat

10 is the Sales sales, Virginia, 01, Israel, Israel, 2001, this is the Sales.

Office in Virginia

20,Accounting,Virginia,13/04/2001,This is the Accounting

Office in Virginia

30,Consulting,Virginia,14/04/2001 12:02:02,This is the Consulting

Office in Virginia

40,Finance,Virginia,987268297,This is the Finance

Office in Virginia

/ / in this way, the load will load the newline character into the following method, but the data format is required to be different.

LOAD DATA

INFILE demo18.dat "fix 101"

INTO TABLE DEPT

REPLACE

FIELDS TERMINATED BY', 'OPTIONALLY ENCLOSED BY' "'

TRAILING NULLCOLS

(DEPTNO

DNAME "upper (: dname)"

LOC "upper (: loc)"

LAST_UPDATED "my_to_date (: last_updated)"

COMMENTS

)

Demo18.dat

10pr Salesweight Virginia01Muraprilwi 2001, "This is the Sales"

Office in Virginia "

20 This is the Accounting Accountingme Virginia 13 Gramps 04According 2001, "

Office in Virginia "

30 This is the Consulting Consulting Virginia 14 Compact 04 Grey, 12:02:02 2001, "

Office in Virginia "

40people finally Virginia987268297, "This is the Finance"

Office in Virginia "

< 3 >

Use the var property

LOAD DATA

INFILE demo19.dat "var 3"

/ / 3 tells the first 3 bytes of each record that the length of the record is as long as 071 of the first record, indicating that the record has 71 bytes.

INTO TABLE DEPT

REPLACE

FIELDS TERMINATED BY','

TRAILING NULLCOLS

(DEPTNO

DNAME "upper (: dname)"

LOC "upper (: loc)"

LAST_UPDATED "my_to_date (: last_updated)"

COMMENTS

)

Demo19.dat

07110 is the Sales, Virginia, 2001, this is the Sales

Office in Virginia

07820,Accounting,Virginia,13/04/2001,This is the Accounting

Office in Virginia

08730,Consulting,Virginia,14/04/2001 12:02:02,This is the Consulting

Office in Virginia

07140,Finance,Virginia,987268297,This is the Finance

Office in Virginia

< 4 >

Use the str property

/ / A new line Terminator win can be defined in the most flexible one: enter enter to feed: chr (13) | | chr (10)

The record in this column ends with a |\ r\ n

Select utl_raw.cast_to_raw ('|'| | chr (13) | | chr (10)) from dual

Results 7C0D0A

LOAD DATA

INFILE demo20.dat "str Xerox 7C0D0A'"

INTO TABLE DEPT

REPLACE

FIELDS TERMINATED BY','

TRAILING NULLCOLS

(DEPTNO

DNAME "upper (: dname)"

LOC "upper (: loc)"

LAST_UPDATED "my_to_date (: last_updated)"

COMMENTS

)

Demo20.dat

10 is the Sales sales, Virginia, 01, Israel, Israel, 2001, this is the Sales.

Office in Virginia |

20,Accounting,Virginia,13/04/2001,This is the Accounting

Office in Virginia |

30,Consulting,Virginia,14/04/2001 12:02:02,This is the Consulting

Office in Virginia |

40,Finance,Virginia,987268297,This is the Finance

Office in Virginia |

=

Data like this uses the nullif clause

10-jan-200002350Flipper seemed unusually hungry today.

10510-jan-200009945Sdivad over three meals.

Id position (1:3) nullif id=blanks / / here can be blanks or some other expression

/ / here is the 1 in the first row of another column that will become null in the database

LOAD DATA

INFILE *

INTO TABLE T

REPLACE

(n position (1:2) integer external nullif naughtified 1'

V position (3:8)

)

BEGINDATA

1 10

20lg

If the log format is in English, you may need to modify the environment variable nls_lang or nls_date_format

=

Guide to the use of SQL*Loader (reproduced)

SQL*Loader is a tool for Oracle database to import external data. It is similar to the Load tool, but has more options, it supports variable loading mode, optional loading and multi-table loading.

How to use the SQL*Loader tool

We can use Oracle's sqlldr tool to import data. For example:

Sqlldr scott/tiger control=loader.ctl

The control file (loader.ctl) loads an external data file (with delimiters). Loader.ctl is as follows:

Load data

Infile'c:\ data\ mydata.csv'

Into table emp

Fields terminated by "," optionally enclosed by'"'

(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 length of the record. "*" means 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 departments

(dept position (02:05) char (4)

Deptname position (08:27) char (20)

)

Begindata

COSC COMPUTER SCIENCE

ENGL ENGLISH LITERATURE

MATH MATHEMATICS

POLY POLITICAL SCIENCE

Tools like Unloader

Oracle does not provide a tool for exporting data to a file. However, we can use SQL*Plus 's select and format data to export 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

Alternatively, you can use UTL_FILE PL/SQL package 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

/

Of course, you can also use third-party tools, such as SQLWays, TOAD for Quest, etc.

Load records of variable length or specified length

Such as:

LOAD DATA

INFILE *

INTO TABLE load_delimited_data

FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY'"'

TRAILING NULLCOLS

(data1

Data2

)

BEGINDATA

11111,AAAAAAAAAA

22222, "A ~ ~ B ~ ~ C ~ D,"

The following is an example of importing fixed position (fixed length) data:

LOAD DATA

INFILE *

INTO TABLE load_positional_data

(data1 POSITION (1:5)

Data2 POSITION (6:15)

)

BEGINDATA

11111AAAAAAAAAA

22222BBBBBBBBBB

Skip data rows:

You can use the "SKIP n" keyword to specify how many rows of data can be skipped on import. Such as:

LOAD DATA

INFILE *

INTO TABLE load_positional_data

SKIP 5

(data1 POSITION (1:5)

Data2 POSITION (6:15)

)

BEGINDATA

11111AAAAAAAAAA

22222BBBBBBBBBB

Modify the data when importing:

When you import data into a database, you can modify the data. Note that this is only suitable for regular imports, not for direct imports. Such as:

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 (1:5) ": 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 BY ","

(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 into multiple tables:

Such as:

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 the selected record:

As in the following example: (01) represents the first character, and (30:37) represents a 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 (01:11) INTEGER EXTERNAL

Call_b_no POSITION (12:29) CHAR

)

Some fields are skipped on import:

Data can be separated by POSTION (XRV). In Oracle8i, you can do this by specifying the FILLER field. The FILLER field is used to skip and ignore fields in the imported data file. Such as:

LOAD DATA

TRUNCATE INTO TABLE T1

FIELDS TERMINATED BY','

(field1

Field2 FILLER

Field3

)

Import multiline records:

You can use one of the following two options to import multiple rows of data as a record:

CONCATENATE:-use when SQL*Loader should combine the same number of physical records together to form. One logical record.

CONTINUEIF-use if a condition indicates that multiple records should be treated as one. Eg. By having a'# 'character in column 1.

Submission of SQL*Loader data:

Typically, it is submitted after the data file data is imported.

You can also specify the number of records per submission 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 on imported tables. Doing so, even when using the ROWS= parameter, can significantly degrade database import performance.

2) DIRECT=TRUE can be added to improve the performance of imported data. Of course, in many cases, this parameter cannot be used.

3) you can close the log of the database by specifying the UNRECOVERABLE option. This option can only be used with direct.

4) you can run multiple import tasks at the same time.

The difference between regular import and direct import:

Regular imports can import data by using INSERT statements. Direct import can skip the relevant logic (DIRECT=TRUE) of the database and import the data directly into the data file.

This is the end of the content of "how to use SQL Loader". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.