============
SQL * Loader
============
伎 襷讌襴 螳る..企 企 り螳 蟆..
.... 覿...
襾..蠍磯 覲伎蟲..
SQL*Loader朱 伎 企 一危襯 讌 ｋ 蟆...
覓語 ...企語 蟾讌 讌 ｌ 給..
讌 ｋ蟇一..insert覓語 郁碓 伎 讌 ｌ 螳 給..
蠏碁 1襷蟇...10襷蟇 蠏 伎 一危襯 讌 ｌ朱る....
企 襷れ...一危 朱 伎...讒..
覲危旧 瑚る 願屋 給...
蠏碁 煙ロ 伎 覦襦 SQL*Loader...
譯 覃讌 伎...襷覦襦..蠍磯 企 一危襯 覿語螳 蟆 伎朱 伎..
願 蠍磯ロ 蟆 ..
5螳讌 殊 襷れ 譯殊伎 ...
れ 蠏 手骸 る..
control file : 一危 襯
data file : 一危襯 願
log file : 襦蠏
bad file : 讌 覈詩 一危
discard file : 讌 覈詩 一危れ 覲企ゼ
朱.. 一危襯 ｋ る 蟆 麹 覓語襯 殊狩 給...
襷 ろ 企 觜るゼ 螳譴 企 一危襯 る る..
螳讌襯 螻ろ 譯殊伎狩...
蠏朱...朱 企語.. ろ瑚 一危襯 ...蠏碁蟆 る 蟇碁Μ讌 蟲一...
...2襷蟇伎 る...15豐 蟇碁Μ手規...朱 pc 襷..
~
...螳讌 螻れ 給..
蠏 螻れ 襷豢手 る狩伎 螳讌 一危 襦 覦覯 螻牛螻 給..
- 朱 蟆暑襯 伎 覦覯
-碁煙ろ 企 一危 襦
-碁煙ろ 讌 企 襦
-企ろ誤 企 一危 襦
-NET* 襦
-SQL 襯 一危
- 讌 蟆暑襯 伎 覦覯
-企朱 豌襴蠍 覓語 SQL覓語 讌 .
-れ 覯襷 企螻 碁煙れ 蠍 覦
-覯朱ゼ 讌 朱蟆暑襯 伎 覦覯覲企 觜襯願 ろ
-語豺伎企襦蠏 覈 企 覦 覈 襦蠏 覲企ゼ ロ讌 .
螳讌 覦覯 觜蟲
朱蟆暑 讌蟆暑
---------------------------------- -------------------------------------------
committ覓語朱 一危 觚襦 覈 覃
襴襦蠏 覲願 語豺伎企襦蠏 --> 襴襦蠏 覲 煙
豺伎企襦蠏 --> 襴襦蠏 覲
企 覈 曙＾蟇 蠍磯蓋, 狩, not null 暑
企ろ碁 襦 螳 企ろ 襦 覿螳
襦譴 insert 碁Μ蟇 螳 襦譴 insert 碁Μ蟇 覿螳
るジ 螳 襦 企 るジ 螳 襦 企
螳
...伎 SQL*Loader 襯 覺...
========================================================================================
C:\>sqlldr
SQL*Loader: Release 8.1.6.0.0 - Production on Jan 29 15:49:42 2002
(c) Copyright 1999 Oracle Corporation. All rights reserved.
覯: SQLLOAD れ=螳 [,れ=螳,...]
れ:
userid -- ORACLE username/password
control -- Control file name
log -- Log file name
bad -- Bad file name
data -- Data file name
discard -- Discard file name
discardmax -- Number of discards to allow (蠍磯蓋螳 all)
skip -- Number of logical records to skip (蠍磯蓋螳 0)
load -- Number of logical records to load (蠍磯蓋螳 all)
errors -- Number of errors to allow (蠍磯蓋螳 50)
rows -- Number of rows in conventional path bind array or between direct p
ath data saves
(蠍磯蓋螳: 蠏 蟆暑 64, 讌 蟆暑 豌)
bindsize -- Size of conventional path bind array in bytes (蠍磯蓋螳 65536)
silent -- Suppress messages during run (header,feedback,errors,discards,partitions)
direct -- use direct path (蠍磯蓋螳 FALSE)
parfile -- parameter file: name of file that contains parameter specifications
parallel -- do parallel load (蠍磯蓋螳 FALSE)
file -- File to allocate extents from
skip_unusable_indexes -- disallow/allow unusable indexes or index partitions (蠍磯蓋螳 FALSE)
skip_index_maintenance -- do not maintain indexes, mark affected indexes as unusable (蠍磯蓋螳 FALSE)
commit_discontinued -- commit loaded rows when load is discontinued (蠍磯蓋螳 FALSE)
readsize -- Size of Read buffer (蠍磯蓋螳 1048576)
譯: 覈轟 襷り覲 豺 轟 れ襦 讌 給
蟆曙一 'sqlload scott/tiger foo';
蟆曙一 'sqlload control=foo userid=scott/tiger'.
伎 豺 襷り覲襯 讌 朱 襷り覲螳
れ 讌 危 給. 襯 れ,
'sqlload scott/tiger control=foo logfile=log' 讌襷,
'sqlload scott/tiger control=foo log' 襷り覲 'log'
豺螳 襷朱 讌 給
========================================================================================
蠏碁 覈 襦語 sqlldr手規襷 豎覲企 企 る 給..
る 襷谿螳讌蟲...
る 覲手??
========================================================================================
/data2/lgeds>sqlldr
SQL*Loader: Release 8.1.6.0.0 - Production on Tue Jan 29 15:48:24 2002
(c) Copyright 1999 Oracle Corporation. All rights reserved.
Usage: SQLLOAD keyword=value [,keyword=value,...]
Valid Keywords:
userid -- ORACLE username/password
control -- Control file name
log -- Log file name
bad -- Bad file name
data -- Data file name
discard -- Discard file name
discardmax -- Number of discards to allow (Default all)
skip -- Number of logical records to skip (Default 0)
load -- Number of logical records to load (Default all)
errors -- Number of errors to allow (Default 50)
rows -- Number of rows in conventional path bind array or between direct path data saves
(Default: Conventional path 64, Direct path all)
bindsize -- Size of conventional path bind array in bytes (Default 65536)
silent -- Suppress messages during run (header,feedback,errors,discards,partitions)
direct -- use direct path (Default FALSE)
parfile -- parameter file: name of file that contains parameter specifications
parallel -- do parallel load (Default FALSE)
file -- File to allocate extents from
skip_unusable_indexes -- disallow/allow unusable indexes or index partitions (Default FALSE)
skip_index_maintenance -- do not maintain indexes, mark affected indexes as unusable (Default FALSE
)
commit_discontinued -- commit loaded rows when load is discontinued (Default FALSE)
readsize -- Size of Read buffer (Default 1048576)
PLEASE NOTE: Command-line parameters may be specified either by
position or by keywords. An example of the former case is 'sqlload
scott/tiger foo'; an example of the latter is 'sqlload control=foo
userid=scott/tiger'. One may specify parameters by position before
but not after parameters specified by keywords. For example,
'sqlload scott/tiger control=foo logfile=log' is allowed, but
'sqlload scott/tiger control=foo log' is not, even though the
position of the parameter 'log' is correct.
========================================================================================
谿谿谿谿谿~~!!
觜朱┨ 觧企...
譴 襦襯 伎 一危襯 る...
sqlldr userid=scott/tiger control='C:\control.ctl' 朱 蟆 ..
譯殊 蟆 errors 朱 旧 豢覿 譯朱朱 蠍一..
蠍磯蓋螳朱 50朱 れ 伎 一危 殊 讌 一危螳 50蟇伎 朱
一危 襯 り 覃豢磯襴蟾...
螳 願姥 覈磯殊 豌 曙 蟆渚 給..
一危 覦 errors 螳 豢覿 れ 譯殊蠍 覦..
...伎 れ襦 企...
襾殊 貉碁， 殊 襷り鍵 ...
ろ碁ゼ 襯 燕 覺....
SQL> create user loader identified by loader
2 default tablespace users;
螳 焔給.
SQL> grant connect, resource to loader;
蟠 覿給.
れ朱 る狩 襦 譯殊伎 蟆朱 れ牛蟶朱蟾...
scott.emp殊 螳 蟲譟磯ゼ 螳讌 企 襷れ 覺..
SQL> connect scott/tiger
郁屋給.
SQL> grant select on emp to loader;
蟠 覿給.
SQL> create table emp
2 as
3 select * from scott.emp
4 where 1=0; ------------> 蠍一 where 1=0 蟆 企 蟲譟磯 覲旧蠍 蟆企..
企 焔給.
$ORACLE_HOME/rdbms/demo 給...
ulcase.* れ 襦 蟯 蟆れ...
蟆覿蠍 ｸ蟆 企讌??? ~ 覓朱 mssql覲企る ｸ讌 讌襷..讒..
~ ...豌 dept企 蟲一...
讒...
sql ろ襴渚碁ゼ ろ..讒..
create table dept
(deptno number(2),
dname char(14) ,
loc char(13) ) ;
ulcase1.ctl 伎..
LOAD DATA
INFILE *
INTO TABLE DEPT
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(DEPTNO, DNAME, LOC)
BEGINDATA
12,RESEARCH,"SARATOGA"
10,"ACCOUNTING",CLEVELAND
11,"ART",SALEM
13,FINANCE,"BOSTON"
21,"SALES",PHILA.
22,"SALES",ROCHESTER
42,"INT'L","SAN FRAN"
貉碁， 殊 伎 危ｴ覲伎覃....
LOAD DATA ----> 螳 貉碁， 殊 豌 れ 螳..
INFILE * ----> '*' 貉碁， 殊 一危螳 れ伎る 襴..
蠍一 一危手骸 貉碁， 殊 覿襴 る..一危 殊 蟆暑襯 ロ ..
INTO TABLE DEPT ----> dept 企 蟆る 襴..
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' -----> 螳 螳 ',' 襦 蟲覿螻 旧朱 '""襦 螻 る 襴..
讀, '"' 讌 ..
(DEPTNO, DNAME, LOC) -----> 一危螳 れ願 貉朱
BEGINDATA ------> 一危螳 る 覩
襾語 一危..
殊 螳蟆 c:\朱 覲旧蟆企..
============================================================================================
C:\>sqlldr userid=loader/loader control='C:\ulcase1.ctl'
SQL*Loader: Release 8.1.6.0.0 - Production on Jan 29 16:14:44 2002
(c) Copyright 1999 Oracle Corporation. All rights reserved.
貉る - 朱Μ 貊 螳 7
============================================================================================
SQL> select count(*) from dept;
COUNT(*)
----------
7 ------------> 一危螳 れ 螳給..
~
譬給...ｸ襴...~
蠍一 蟆...麹 襷給...
朱...貉碁， 手骸 一危襷 る 襦 ろ 螳ロ...
蠏碁...10襷蟇..100襷蟇 一危 る螳 碁..
ulcase1.ctl 伎 伎 覺....
LOAD DATA
INFILE *
APPEND ---------> APPEND 朱 れ 豢螳...
INTO TABLE DEPT
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(DEPTNO, DNAME, LOC)
BEGINDATA
12,RESEARCH,"SARATOGA"
100,"ACCOUNTING",CLEVELAND -------> 10 100朱 螻豎れ..
11,"ART",SALEM
13,FINANCE,"BOSTON"
21,"SALES",PHILA.
22,"SALES",ROCHESTER
42,"INT'L","SAN FRAN"
< 谿瑚 >
replace 企 蠍一ヾ 覈 (delete)螻 insert
append 襦 蠍一ヾ 一危 豢螳
insert 觜 企 ｌ
truncate 企 蠍一ヾ 一危襯 覈 truncate 螻 insert
================================================================================
C:\>sqlldr userid=loader/loader control='C:\ulcase1.ctl'
SQL*Loader: Release 8.1.6.0.0 - Production on Jan 29 16:20:18 2002
(c) Copyright 1999 Oracle Corporation. All rights reserved.
貉る - 朱Μ 貊 螳 7
================================================================================
SQL> RUN
1* select count(*) from dept
COUNT(*)
----------
13 --------------> 13螳..
覓朱 朱 貉碁， 手骸 一危螳 朱 讌襷....
一危 る螳 蟆曙磯 覓語螳 ..
蠏碁覩襦 れ螻 螳 5螳讌 殊 覈 危 譴...
================================================================================
C:\>sqlldr userid=loader/loader control='C:\ulcase1.ctl' log='c:\ulcase1.log' ba
d='c:\ulcase1.bad' discard='c:\ulcase1.dsc'
SQL*Loader: Release 8.1.6.0.0 - Production on Jan 29 16:23:46 2002
(c) Copyright 1999 Oracle Corporation. All rights reserved.
貉る - 朱Μ 貊 螳 7
================================================================================
SQL> run
1* select count(*) from dept
COUNT(*)
----------
19
..伎 一危螳 れ願 蟆 襦蠏語 一危襯 危ｴ覲企襦 ..
ulcase1.log
================================================================================
SQL*Loader: Release 8.1.6.0.0 - Production on Jan 29 16:23:46 2002
(c) Copyright 1999 Oracle Corporation. All rights reserved.
: C:\ulcase1.ctl
一危 : C:\ulcase1.ctl
覈視 : c:\ulcase1.bad
蠍 : c:\ulcase1.dsc
(覈 蠍磯 貊 )
襦 蟇伎: ALL
蟇伎: 0
る: 50
覦碁 覦一: 64 , 豕 65536 覦危
螻: 讌
蟆暑: 蠏
企 DEPT, 襦給 螳螳 朱Μ 貊襦覿
企 麹 Insert 旧: APPEND
企 豺 Len Term Encl 一危一
------------------------------ ---------- ----- ---- ---- ---------------------
DEPTNO FIRST * , O(") CHARACTER
DNAME NEXT * , O(") CHARACTER
LOC NEXT * , O(") CHARACTER
貊 2: 蠍郁 - 企 DEPT, DEPTNO る
ORA-01438: 讌 襯 豐螻狩 螳 伎 讌給
企 DEPT:
6 襦給.
一危 る 覓語 1 ()螳 襦讌 給
覈 WHEN ろ 0 ()螳 襦讌 給
覈 螳 NULL伎伎 0 ()螳 襦讌 給
覦碁 覦一伎 豪 : 49536覦危(64 )
覦碁 覦一伎語 覃覈襴 豪 : 0 覦危
給 朱Μ 貊 螻: 0
曙企 朱Μ 貊 螻: 7
蟇磯 朱Μ 貊 螻: 1
蠍磯 朱Μ 貊 螻: 0
Jan 29 16:23:46 2002 ろ 螳
Jan 29 16:23:48 2002 ろ 譬襭
蟆所骸 螳: 00:00:02.49
CPU 螳: 00:00:00.07
================================================================================
ulcase1.bad
================================================================================
100,"ACCOUNTING",CLEVELAND
================================================================================
襦蠏碁ゼ 危ｴ覲企...
'ORA-01438: 讌 襯 豐螻狩 螳 伎 讌給'
朱 蟆 覲伎..
10 れ願 蟆 100 れ願 貉殊 れ願 る 詞伎...~
...伎 蠍一襯 朱...
襾語 貉碁， 殊 伎襷 る螻 SQL*Loader襯 襷豺襦 蟆企..
ulcase2.ctl
================================================================================
LOAD DATA
INFILE 'ulcase2.dat' --------> 一危螳 れ 殊 蟆暑
INTO TABLE EMP
( EMPNO POSITION(01:04) INTEGER EXTERNAL,
ENAME POSITION(06:15) CHAR,
JOB POSITION(17:25) CHAR,
MGR POSITION(27:30) INTEGER EXTERNAL,
SAL POSITION(32:39) DECIMAL EXTERNAL,
COMM POSITION(41:48) DECIMAL EXTERNAL,
DEPTNO POSITION(50:51) INTEGER EXTERNAL)
EMPNO POSITION(01:04) INTEGER EXTERNAL 覩碁 empno 貉る殊 一危
01伎 04 蟾讌企る朱 蟆螻 integer朱 伎豌伎 殊 貉殊 一危
襷豢る 覩語...
ulcase2.dat
================================================================================
7782 CLARK MANAGER 7839 2572.50 10
7839 KING PRESIDENT 5500.00 10
7934 MILLER CLERK 7782 920.00 10
7566 JONES MANAGER 7839 3123.75 20
7499 ALLEN SALESMAN 7698 1600.00 300.00 30
7654 MARTIN SALESMAN 7698 1312.50 1400.00 30
7658 CHAN ANALYST 7566 3450.00 20
ulcase3.ctl
================================================================================
LOAD DATA
INFILE *
APPEND ----------> 蠍一ヾ 貉殊 一危 豢螳....
INTO TABLE EMP
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
(empno, ename, job, mgr,
hiredate DATE(20) "DD-Month-YYYY", -------> 讌
sal, comm,
deptno CHAR TERMINATED BY ':', -------> 貉 蟲覿 譬蟆一...讀, れ覿磯 ':' 朱 貉 蟲覿
projno,
loadseq SEQUENCE(MAX,1) ) ---------> 貉殊朱 れ..
BEGINDATA
7782, "Clark", "Manager", 7839, 09-June-1981, 2572.50,, 10:101
7839, "King", "President", , 17-November-1981, 5500.00,, 10:102
7934, "Miller", "Clerk", 7782, 23-January-1982, 920.00,, 10:102
7566, "Jones", "Manager", 7839, 02-April-1981, 3123.75,, 20:101
7499, "Allen", "Salesman", 7698, 20-February-1981, 1600.00, 300.00, 30:103
7654, "Martin", "Salesman", 7698, 28-September-1981, 1312.50, 1400.00, 30:103
7658, "Chan", "Analyst", 7566, 03-May-1982, 3450,, 20:101
SQL> run ---------> 一危 蟆郁骸...
1* select * from emp
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO PROJNO LOADSEQ
---------- ---------- --------- ---------- -------- ---------- ---------- ---------- ---------- ----
7782 Clark Manager 7839 81/06/09 2572.5 10 101 1
7839 King President 81/11/17 5500 10 102 2
7934 Miller Clerk 7782 82/01/23 920 10 102 3
7566 Jones Manager 7839 81/04/02 3123.75 20 101 4
7499 Allen Salesman 7698 81/02/20 1600 300 30 103 5
7654 Martin Salesman 7698 81/09/28 1312.5 1400 30 103 6
7658 Chan Analyst 7566 82/05/03 3450 20 101 7
7 螳 給.
ulcase4.ctl
================================================================================
LOAD DATA
INFILE "ulcase4.dat"
DISCARDFILE "ulcase4.dis"
DISCARDMAX 999 ----------> れ拘 殊 豕 999蟾讌
REPLACE
CONTINUEIF (1) = '*' -----------> 螳 '*'襦 豌危...
INTO TABLE EMP
( EMPNO POSITION(01:04) INTEGER EXTERNAL,
ENAME POSITION(06:15) CHAR,
JOB POSITION(17:25) CHAR,
MGR POSITION(27:30) INTEGER EXTERNAL,
SAL POSITION(32:39) DECIMAL EXTERNAL,
COMM POSITION(41:48) DECIMAL EXTERNAL,
DEPTNO POSITION(50:51) INTEGER EXTERNAL,
HIREDATE POSITION(52:60) INTEGER EXTERNAL)
ulcase5.ctl
================================================================================
LOAD DATA
INFILE 'ulcase5.dat'
BADFILE 'ulcase5.bad'
DISCARDFILE 'ulcase5.dis'
REPLACE
INTO TABLE EMP
(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
-- PROJ has two columns, both not null: EMPNO and PROJNO
WHEN PROJNO != ' '
(EMPNO POSITION(1:4) INTEGER EXTERNAL,
PROJNO POSITION(25:27) INTEGER EXTERNAL) -- 1st proj
INTO TABLE PROJ
WHEN PROJNO != ' '
(EMPNO POSITION(1:4) INTEGER EXTERNAL,
PROJNO POSITION(29:31) INTEGER EXTERNAL) -- 2nd proj
INTO TABLE PROJ
WHEN PROJNO != ' '
(EMPNO POSITION(1:4) INTEGER EXTERNAL,
PROJNO POSITION(33:35) INTEGER EXTERNAL) -- 3rd proj
ulcase5.dat
================================================================================
1234 BAKER 10 9999 101 102 103
1234 JOKER 10 9999 777 888 999
2664 YOUNG 20 2893 425 abc 102
5321 OTOOLE 10 9999 321 55 40
2134 FARMER 20 4555 236 456
2414 LITTLE 20 5634 236 456 40
6542 LEE 10 4532 102 321 14
2849 EDDS xx 4555 294 40
4532 PERKINS 10 9999 40
1244 HUNT 11 3452 665 133 456
123 DOOLITTLE 12 9940 132
1453 MACDONALD 25 5532 200
蟆郁記 螳 企 一危 手讌螻 一危襯 蟆..
蟆 覲 蟆 when 蟆碁...
願朱...一危 殊 襷讌襷 5覯讌 貉殊 一危覿...
7覯讌 貉殊 一危蟾讌 覈 proj 企 projno 貉殊 蟆 覲伎...
一危 蟆郁骸.........
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
1234 BAKER 9999 10
1234 JOKER 9999 10
5321 OTOOLE 9999 10
2134 FARMER 4555 20
2414 LITTLE 5634 20
6542 LEE 4532 10
4532 PERKINS 9999 10
1244 HUNT 3452 11
123 DOOLITTLE 9940 12
1453 MACDONALD 5532 25
10 螳 給.
SQL> select * from proj;
EMPNO PROJNO
---------- ----------
1234 101
1234 777
5321 321
2134 236
2414 236
6542 102
4532 40
1244 665
1234 102
1234 888
5321 55
2134 456
2414 456
6542 321
1244 133
1453 200
1234 103
1234 999
5321 40
2414 40
6542 14
1244 456
123 132
23 螳 給.
ulcase6.dat
================================================================================
load data
infile 'ulcase6.dat'
replace
into table emp
sorted indexes (empix) -----------> 碁煙る ...讌蟆暑朱襷 螳..
(empno position(1:4),
ename position(6:15),
job position(17:25),
mgr position(27:30) nullif mgr=blanks, --------> 螳企 螻給葦朱 ｋ..(螻給葦 螻 襴..)
sal position(32:39) nullif sal=blanks,
comm position(41:48) nullif comm=blanks,
deptno position(50:51) nullif empno=blanks)
襾...伎 蟾讌 蟆企...
....る狩伎 Bulletin 伎..
谿瑚蠍 覦...
IMAGE FILE LOAD蠍
======================
300,000 byte binary(image)file load蠍 伎 螳 control
file 燕.
options (bindsize=350000)
load data
infile image.gif "fix 60000"
replace
concatenate 5
into table xx
(f1 raw(300000))
* bindsize option SQL*Loder螳 覲危 64K buffer襯 蟆 ♀ 譴.
* "fix 60000" o/s 郁 覈轟朱 SQL*Loder螳 data file 60,000 bytes襯 所 螻,
願 physical record襦 豬蠍蟆
. 願 覲危 record delimiter襦 linefeed character襯
谿場 蟆 .
* concatenate 5 SQL*Loder螳 れ 螳 physical record襦 螳 database
record襯 襷 蟆 襷. logical record螳 database long raw
column朱 讌 れ願蟆 .
SQL*Loder螳 覯 file single record豌( "fix 300000" 伎)
所 . 覃 願 file襦 覿 曙企企 record size
伎 operating system 螳讌 螻 64K 蟇碁Υ 蠍 覓語企.
襷 伎 螳 蟆曙一 SQL*Loder-510 error螳 覦.
"fix n" option UNIX, PC 覈 . VMX "fix=n"讀
"fix=60000"企.
300,000 byte record襦 讌蠍 覓語 覈 image 蠍郁 螳蠍
file image襯 load 訖企. 願 image 襷
option 螳讌 襦 るジ control file 螳讌螻 伎 る 蟆企.
蠏碁Μ螻 れ data覲企 control file raw 蠍語企ゼ 覈伎 螳 る 蠍語
襷 SQL*Loder螳 磯蠍 螳 豈 ｊ 蟆企.
LOADER 襯 牛 LONGRAW DATA 襴
=======================================
CREATE TABLE MYPICS
(image long raw);
Table created.
LOAD DATA
INFILE NX6.jpg "fix 65535"
CONCATENATE 10
INTO TABLE MYPICS
fields terminated by ':'
(IMAGE raw (120000))
The command line would look like this
sqlldr userid=scott/tiger control=image.ctl bindsize=250000 logfile=logimg
企 Row 螳 譟伎 Table 蟆曙 Append 襯 豢螳.
(IMAGE2.CTL)
LOAD DATA
INFILE NX6.jpg "fix 65535"
APPEND
CONCATENATE 10
INTO TABLE MYPICS
fields terminated by ':'
(IMAGE raw (120000))
sqlldr userid=scott/tiger control=image2.ctl bindsize=250000 log=log2imp
==================================================================
SQL*LOADER(8i) variable size field襯 table load蠍 (FILLER)
==================================================================
SQL*LOADER variable length record variable size field襯 螳讌 data file
table load 覦覯 螳螻 .
( 8i new feature FILLER )
SQL*LOADER SYNTAX
------------------
table load螻 control file 螳 覃 .
...
INTO TABLE emp
...
INTO TABLE emp1
...
fixed length field 螳讌 data file table 螳 data load螻
る 螳.
...
INTO TABLE emp
(empno POSITION(1:4) INTEGER EXTERNAL,
...
INTO TABLE emp1
(empno POSITION(1:4) INTEGER EXTERNAL,
...
螳 讓 table empno field 螳螳 load data襦覿 1-4蟾讌襯 load
. 蠏碁 field 蠍語願 螳覲企朱 螳 POSITION 螳 field .
1>
create table one (
field_1 varchar2(20),
field_2 varchar2(20),
empno varchar(10) );
create table two (
field_3 varchar2(20),
empno varchar(10) );
load record螳 comma襦 伎覃 蠍語願 螳覲企手 螳.
<< data.txt >> - load data file
"this is field 1","this is field 2",12345678,"this is field 4"
<< test.ctl >> - control file
load data infile 'data.txt'
discardfile 'discard.txt'
into table one
replace
fields terminated by ","
optionally enclosed by '"' (
field_1,
field_2,
empno )
into table two
replace
fields terminated by ","
optionally enclosed by '"' (
field_3,
dummy1 filler position(1),
dummy2 filler,
empno )
dummy1 field filler襦 碁. filler襦 誤覃 table load讌 .
two朱 table dummy1企朱 field 朱 position(1) current record
豌覿 伎 豌覯讌 field dummy1 filler item loadる 蟆 襷.
蠏碁Μ螻 覯讌 field dummy2 filler item load. 碁讌 field, one企朱
table load employee number two朱 table load 蟆企,
<< ろ >>
$sqlload scott/tiger control=test.ctl data=data.txt log=test.log bindsize=300000
$sqlplus scott/tiger
SQL> select * from one;
FIELD_1 FIELD_2 EMPNO
-------------------- -------------------- ----------
this is field 1 this is field 2 12345678
SQL> select * from two;
FIELD_3 EMPNO
-------------------- ----------
this is field 4 12345678
2>
create table testA (c1 number, c2 varchar2(10), c3 varchar2(10));
<< data1.txt >> - load data file
7782,SALES,CLARK
7839,MKTG,MILLER
7934,DEV,JONES
<< test1.ctl >>
LOAD DATA
INFILE 'data1.txt'
INTO TABLE testA
REPLACE
FIELDS TERMINATED BY ","
(
c1 INTEGER EXTERNAL,
c2 FILLER CHAR,
c3 CHAR
)
<< ろ >>
$ sqlload scott/tiger control=test1.ctl data=data1.txt log=test1.log
$ sqlplus scott/tiger
SQL> select * from testA;
C1 C2 C3
---------- ---------- ----------
7782 CLARK
7839 MILLER
7934 JONES
~~
襾...殊 螻..企 襷れ伎 襦 讌襷...
願 襷 觜讌蟆企...~~
... れ 襷 襷 給 譬 襭 螳^^ -- 譟郁唄 2017-04-02 22:26:01
... れ. 蠍 蠍 る. 2001瑚? 蠏語れ 蠍瑚碓 螳一... -- 伎 2017-04-02 23:30:27
襷 螻襷 襭 . 手. -- 伎蠏 2018-02-08 21:04:20