_ | 覦覈襦 | 豕蠏手 | 殊螳 | 譯殊碁
FrontPage › SQLLoader覦覯
Describe SQLLoader覦覯 here

============
SQL * Loader
============

 伎 襷讌襴 螳る..企 企  り螳   蟆..
 .... 覿...
襾..蠍磯 覲伎蟲..

SQL*Loader朱 伎 企 一危襯 讌 k 蟆...
覓語 ...企語 蟾讌 讌 l  給..
讌 k蟇一..insert覓語 郁碓 伎 讌 l 螳 給..
蠏碁 1襷蟇...10襷蟇  蠏 伎 一危襯 讌 l朱る....
企 襷れ...一危 朱 伎...讒..
覲危旧 瑚る 願屋  給...
蠏碁 煙ロ 伎 覦襦 SQL*Loader...
譯 覃讌 伎...襷覦襦..蠍磯 企 一危襯  覿語螳 蟆 伎朱 伎..

願 蠍磯ロ 蟆 ..
5螳讌 殊 襷れ 譯殊伎 ...
れ 蠏 手骸 る..

control file : 一危 襯   
data file    : 一危襯 願  
log file     : 襦蠏 
bad file     : 讌 覈詩 一危
discard file : 讌 覈詩 一危れ  覲企ゼ 


朱.. 一危襯 k る 蟆 麹 覓語襯 殊狩  給...
襷 ろ 企 觜るゼ 螳譴  企  一危襯 る  る..
螳讌襯 螻ろ 譯殊伎狩...
蠏朱...朱 企語.. ろ瑚  一危襯 ...蠏碁蟆 る 蟇碁Μ讌 蟲一...
...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                  觜  企 l  

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,    --------> 螳企 螻給葦朱 k..(螻給葦 螻 襴..)
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螳 磯蠍 螳 豈 j  蟆企.


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

襷 覦螻 給! -- 磯 2017-04-06 19:42:26

所 螳~~~ -- 襷襭 2017-08-11 15:10:43

襷 螻襷 襭 . 手. -- 伎蠏 2018-02-08 21:04:20
蠍 蠍郁鍵..
企: : るジ讓曙 襦螻豺 企Ν 譯殊語. 襦螻豺
EditText : Print : Mobile : FindPage : DeletePage : LikePages : Powered by MoniWiki : Last modified 2018-04-13 23:12:52

蟇磯Μ覲企 豢糾碓襴襯 襷 襷れ企. 襷 襷 覃 螳 蟇磯Μ螳 蠍郁 蠏碁 谿語朱 蠏瑚 豢糾碓襴螳 . (豢)