SPOOL, Sqlloader Data Export import a little summary

1. Sqlloader Control File

//********************************************************************************//
Basic format:
LOAD DATA
INFILE ' T.DAT '//Data file to import (format 1)
INFILE ' TT. DAT '//import multiple files (can be used in parallel with format 1)
INFILE *//the content to be imported is in the control file the following begindata is the imported content (and format 1 mutex use)

APPEND into table table_name//Specify the loaded table (here are several loading methods)

Here are 4 ways to load tables
APPEND//The data on the original table is added to the back.
INSERT//load empty table, if the original table has data Sqlloader will stop the default value
REPLACE//original table with data the original data will be deleted all
TRUNCATE//Specifies that the same content as replace will delete the existing data with the TRUNCATE statement

Badfile ' C:bad. TXT '//Specify Bad file address

Fields TERMINATED by ', ' optionally enclosed by ' "'
Load this data: "10", "20", "30", "40", "50"
TERMINATED by X ' 09 '//in hexadecimal format ' 09 ' indicates that the text file is delimited with TAB key
Sample text data: "10" "20" "30" "40" "50"
TERMINATED by whitespace//load this data: "Ten" "LG" "LG"

TRAILING nullcols ************* table field does not have a corresponding value when NULL is allowed

The following is a table field
(Col_1, col_2, Col_filler FILLER//FILLER keyword The value of this column will not be loaded)

The specified terminated can also be at the beginning of the table in the internal Fields section of the table
When you do not declare field TERMINATED by ', ' You can also declare the fields individually
// (
Col_1 [Interger EXTERNAL] TERMINATED by ', ',
col_2 [DATE "dd-mon-yyy"] TERMINATED by ', ',
Col_3 [CHAR] TERMINATED by ', ' optionally enclosed by ' "'
// )
When not declared field TERMINATED by ', ' use location to tell fields to load data
// (
Col_1 POSITION (1:2),
Col_2 POSITION (3:10),
Col_3 POSITION (*:16),//Start position of this field at the end of the previous field
Col_4 POSITION (3:10) CHAR (8),//Specify the type of field
Col_5 POSITION (3:10) "TRIM (: Col_5)",//squeeze both ends of the space
Col_6 POSITION (3:10) "SEQ. Nextval ",//Fetch sequence value
// )

Begindata//corresponding to the beginning of the INFILE * The content to be imported is in the control file
10,20,30
20,30,40
//********************************************************************************//
Control File Example:
Note that the value after the begindata cannot have spaces before

1 * * * Normal load
LOAD DATA
INFILE *
REPLACE into TABLE DEPT
Fields TERMINATED by ', ' optionally enclosed by ' "'
(DEPTNO,
Dname,
LOC
)
Begindata
10,sales, "" "USA" ""
20,accounting, "Virginia,usa"
30,consulting,virginia
40,finance,virginia
"FINANCE", "", VIRGINIA//LOC column will be empty
"FINANCE", VIRGINIA//LOC column will be empty

2 * * * case fields TERMINATED by whitespace and fields TERMINATED by X ' 09 '
LOAD DATA
INFILE *
REPLACE into TABLE DEPT
Fields TERMINATED by whitespace
--TERMINATED by X ' 09 '
(DEPTNO,
Dname,
LOC
)
Begindata
Ten Sales Virginia

3 * * * Specify not to load that column
LOAD DATA
INFILE *
REPLACE into TABLE DEPT
Fields TERMINATED by ', ' optionally enclosed by ' "'
(DEPTNO,
Filler_1 FILLER,//below "Something not to be Loaded" will not be loaded
Dname,
LOC
)
Begindata
20,something not to be loaded,accounting, "Virginia,usa"

4 * * * Position's
LOAD DATA
INFILE *
REPLACE into TABLE DEPT
(DEPTNO POSITION (1:2),
Dname POSITION (*:16),//Start position of this field at the end of the previous field
LOC POSITION (*:29),
Entire_line POSITION (1:29)
)
Begindata
10ACCOUNTING Virginia,usa

5 * * * * Use of a function date to express the use of TRAILING Nullcols
LOAD DATA
INFILE *
REPLACE into TABLE DEPT
Fields TERMINATED by ', '
TRAILING Nullcols//In fact, the following entire_line in the data behind the begindata is not directly corresponding
The value of the column if the first line is changed to 10,sales,virginia,1/5/2000, you don't have to trailing nullcols.
(DEPTNO,
Dname "UPPER (NAME)",//Use function
Loc "UPPER (: Loc)",
last_updated Date ' dd/mm/yyyy ', one way of expression of dates and ' dd-mon-yyyy ', etc.
Entire_line "eptno| | name| |: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 * * * * Use custom functions//Resolved time issues
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 HH24: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 are not NULL;
END LOOP;

IF (L_return is NULL)
Then
L_return: =
New_time (to_date (' 01011970 ', ' ddmmyyyy ') + 1/24/60/60 *
p_string, ' GMT ', ' EST ');
END IF;

RETURN L_return;
END;
/

LOAD DATA
INFILE *
REPLACE into TABLE DEPT
Fields TERMINATED by ', '
TRAILING Nullcols
(DEPTNO,
Dname "UPPER (NAME)",
Loc "UPPER (: Loc)",
Last_updated "My_to_date (: last_updated)"//using a custom function
)
Begindata
10,sales,virginia,01-april-2001
20,accounting,virginia,13/04/2001
30,consulting,virginia,14/04/2001 12:02:02
40,finance,virginia,987268297
50,finance,virginia,02-apr-2001
60,finance,virginia,not a Date

7 * * * Merge multiple rows of records into one row
LOAD DATA
INFILE *
Concatenate 3//by keyword concatenate a few lines of records as a row of records
Into TABLE DEPT
REPLACE//Note This example format is somewhat different from the front
Fields TERMINATED by ', '
(DEPTNO,
Dname "UPPER (NAME)",
Loc "UPPER (: Loc)",
last_updated DATE ' dd/mm/yyyy '
)
Begindata
10,sales,//actually these 3 lines as a row 10,sales,virginia,1/5/2000
Virginia,
1/5/2000
This column with Continueif list= "," can also
Tell Sqlldr to find a comma at the end of each line to append the next line to the previous line

LOAD DATA
INFILE *
Continueif this (1:1) = '-'//Find the beginning of each line if there is a connection character-the next line is connected to a line
such as -10,sales,virginia,
1/5/2000 is a row 10,sales,virginia,1/5/2000
Where 1:1 means starting from the first line and ending at the first line with Continueif NEXT but the Continueif list is ideal
Into TABLE DEPT
REPLACE
Fields TERMINATED by ', '
(DEPTNO,
Dname "Upper (:d name)",
Loc "Upper (: Loc)",
last_updated Date ' dd/mm/yyyy '
)
Begindata//But it doesn't seem to work like the right.
-10,sales,virginia, -10,sales,virginia,
1/5/2000 1/5/2000
-40, 40,finance,virginia,13/04/2001
finance,virginia,13/04/2001

8 * * * Load the line number of each line
LOAD DATA
INFILE *
Into TABLE T
REPLACE
(seqno recnum//Load line number per line
TEXT POSITION (1:1024))
Begindata
FSDFASJ//Automatically assigns a line number to the Seqno field loaded in table t this line is 1
FASDJFASDFL//This line is 2 ...

9 * * * Load data with newline characters
Note: UNIX and WINDOWS are different N & N
< 1 > characters with a non-newline character
LOAD DATA
INFILE *
Into TABLE DEPT
REPLACE
Fields TERMINATED by ', '
TRAILING Nullcols
(DEPTNO,
Dname "UPPER (NAME)",
Loc "UPPER (: Loc)",
Last_updated "My_to_date (: last_updated)",
COMMENTS "replace (: COMMENTS, ' N ', CHR)"//Replace with help converting line breaks
)
Begindata
10,sales,virginia,01-april-2001,this is the Salesnoffice in Virginia
20,accounting,virginia,13/04/2001,this is the Accountingnoffice in Virginia
30,consulting,virginia,14/04/2001 12:02:02,this is the Consultingnoffice in Virginia
40,finance,virginia,987268297,this is the Financenoffice in Virginia

< 2 > Using the Fix property
LOAD DATA
INFILE DEMO17. DAT "FIX 101"
Into TABLE DEPT
REPLACE
Fields TERMINATED by ', '
TRAILING Nullcols
(DEPTNO,
Dname "UPPER (NAME)",
Loc "UPPER (: Loc)",
Last_updated "My_to_date (: last_updated)",
COMMENTS
)
Begindata
10,sales,virginia,01-april-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

This loads the newline character into the database, but the following method does not, but requires the data to be in a different format

LOAD DATA
INFILE DEMO18. DAT "FIX 101"
Into TABLE DEPT
REPLACE
Fields TERMINATED by ', ' optionally enclosed by ' "'
TRAILING Nullcols
(DEPTNO,
Dname "UPPER (NAME)",
Loc "UPPER (: Loc)",
Last_updated "My_to_date (: last_updated)",
COMMENTS
)
Begindata
10,SALES,VIRGINIA,01-APRIL-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 "

< 3 > Using the var property
LOAD DATA
INFILE DEMO19. DAT "VAR 3"
3 tells the first 3 bytes of each record to indicate the length of the record, as 071 of the first record indicates that the record has 71 bytes
Into TABLE DEPT
REPLACE
Fields TERMINATED by ', '
TRAILING Nullcols
(DEPTNO,
Dname "UPPER (NAME)",
Loc "UPPER (: Loc)",
Last_updated "My_to_date (: last_updated)",
COMMENTS
)
Begindata
07110,sales,virginia,01-april-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 > Using the str attribute
The most flexible one can define a new end-of-line character WIN carriage return to line: CHR (13) | | CHR (10)

The records in this column are in a| RN End of
SELECT Utl_raw. Cast_to_raw (' | ' | | CHR (13) | | CHR (Ten)) from DUAL;
Result 7c0d0a

LOAD DATA
INFILE DEMO20. DAT "STR X ' 7c0d0a '"
Into TABLE DEPT
REPLACE
Fields TERMINATED by ', '
TRAILING Nullcols
(DEPTNO,
Dname "UPPER (NAME)",
Loc "UPPER (: Loc)",
Last_updated "My_to_date (: last_updated)",
COMMENTS
)
Begindata
10,sales,virginia,01-april-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|

10 * * * * * like this data with the NULLIF clause

10-jan-200002350flipper seemed unusually hungry today.
10510-jan-200009945spread over three meals.

ID POSITION (1:3) Nullif id=blanks//This can be a BLANKS or another expression
Here's another one. 1 of the first row will become NULL in the database
LOAD DATA
INFILE *
Into TABLE T
REPLACE
(N POSITION (1:2) INTEGER EXTERNAL nullif n= ' 1 ',
V POSITION (3:8)
)
Begindata
1 10
20lg
//********************************************************************************//
Sqlloader's command:
Sqlldr userid=sys/[email protected]_service control=xxxx. CTL log=xxxx. LOG bindsize=1048576 rows=100
errors=10000 readsize=2097152 silent= (header,feedback)
The help on these parameters can be obtained by executing SQLLDR directly on the command line, which indicates that bindsize should not be greater than the value of readsize.

Some formatting suggestions for spool exporting text data:
Sql*plus Environment Setting set NEWPAGE NONE HEADING OFF SPACE 0 PAGESIZE 0 trimout on Trimspool on Linesize 2500
Note: Linesize to be slightly larger, lest the data be truncated, it should be combined with the corresponding trimspool to prevent the exported text has too many trailing spaces.
However, if the linesize setting is too large, it will greatly reduce the speed of the export, in addition to the export under Windows is best not to export with plsql, slower,
Use the Sqlplus command under commend to minimize the execution of the window.
For SQL spool data, it is best to define your own format to facilitate our import, such as the example below:
SELECT
JBSJ. jsjdm| | CHR (9) | |
JBSJ. zzjgdm| | CHR (9) | |
JBSJ. yyzzh| | CHR (9) | |
Replace (replace (JBSJ. NSRMC,CHR (Ten)), CHR (13)) | | CHR (9) | |
Qyry. zjlxdm| | CHR (9) | |
Replace (replace (Qyry. ZJHM,CHR (Ten)), CHR (13)) | | CHR (9) | |
Replace (replace (Qyry. XM,CHR (Ten)), CHR (13)) | | CHR (9) | |
Replace (replace (JBSJ. ZCDZ,CHR (Ten)), CHR (13)) | | CHR (9) | |
Replace (replace (JBSJ. JYDZ,CHR (Ten)), CHR (13)) | | CHR (9) | |
Replace (replace (JBSJ. JYFW,CHR (Ten)), CHR (13)) | | CHR (9) | |
Replace (replace (JBSJ. JYDZYB,CHR (Ten)), CHR (13)) | | CHR (9) | |
Zclx. djzclxdm| | CHR (9) | |
To_char (JBSJ. KYDJRQ, ' yyyy-mm-dd ') | | CHR (9) | |
JBSJ. swjgzzjgdm| | CHR (9) | |
Replace (replace (SWJGZZJG. SWJGZZJGMC,CHR (Ten)), CHR (13)) | | CHR (9) | |
JBSJ. zczbje| | CHR (9) | |
JBSJ. nsrzt| | CHR (9) | |
Nsrzt. Nsrztmc
From Djdb. DJ_JL_JBSJ JBSJ,
Djdb. Dj_jl_qyry Qyry,
Dmdb. GY_DM_SWJGZZJG SWJGZZJG,
Dmdb. DJ_DM_DJZCLX ZCLX,
Dmdb. Dj_dm_nsrzt Nsrzt
WHERE JBSJ. Djzclxdm=zclx. Djzclxdm
and JBSJ. Jsjdm=qyry. Jsjdm
and Qyry. zwdm= ' 01 '
and JBSJ. Swjgzzjgdm=swjgzzjg. Swjgzzjgdm
and JBSJ. Nsrzt=nsrzt. Nsrztdm
and JBSJ. Nsrzt!=90
and JBSJ. Kydjrq < To_date (' 20040701 ', ' YYYYMMDD ')
For a field containing a lot of carriage return line feed should be given and filtered to form a more formal text file.

Typically, we use the spool method to export a table from a database to a text file using two methods, such as the following:
Method One: Use the following format script
Set COLSEP '------Setting column separators
Set Trimspool on
Set Linesize 120
Set PageSize 2000
Set NewPage 1
Set Heading off
Set term off
Spool path + file name
SELECT * FROM TableName;
Spool off
Method Two: Use the following script
Set Trimspool on
Set Linesize 120
Set PageSize 2000
Set NewPage 1
Set Heading off
Set term off
Spool path + file name
Select Col1| | ', ' | | col2| | ', ' | | col3| | ', ' | | col4| | '.. ' from TableName;
Spool off
Compare the above method, that is, method one using the set delimiter and then by the sqlplus themselves using the set delimiter to split the field, method two will be the delimiter stitching in the SELECT statement, that is, manually control the output format.
In practice, I found that the data obtained through the method of a large uncertainty, this method of data derived from SQL LDR when the likelihood of error is more than 95%, especially for a large number of data tables, such as 1 million records of the table is more so, and exported data file crazy.
and method two exported data file format is very regular, the size of the data file may be the method of about 1/4. When the data files are imported by SQLLDR, the possibility of error is very small, and the basic can be imported successfully.
Therefore, in practice I suggest that you use method two manual to control the format of the spool file, so as to reduce the possibility of error, avoid a lot of detours.

SPOOL, Sqlloader Data Export import a little summary