课程第3次

ulcase4.ctl的改写：

1. LOAD DATA
   
2. INFILE "ulcase4.dat"
   
3. DISCARDFILE "ulcase4.dis"
   
4. DISCARDMAX 999
   
5. REPLACE
   
6. CONTINUEIF (1) = '*'
   
7. INTO TABLE EMP
   
8. 
   
9. ( EMPNO    POSITION(01:04) INTEGER EXTERNAL,
   
10.   ENAME    POSITION(06:15) CHAR,
    
11.   JOB      POSITION(17:25) CHAR,
    
12.   MGR      POSITION(27:30) INTEGER EXTERNAL,
    
13.   SAL      POSITION(32:39) DECIMAL EXTERNAL,
    
14.   COMM     POSITION(41:48) DECIMAL EXTERNAL,
    
15.   DEPTNO   POSITION(50:51) INTEGER EXTERNAL,
    
16.   HIREDATE POSITION(52:60) date(9) "DD-MON-RR"  )

复制代码

直接路径加载之后，尤其要看日志。
ulcase7可以用一些sql函数。如果要导入的文本文件里头，有超出英文的字符，要确认“码表”。

1. LOAD DATA
   
2. characterset ZHS16GBK
   
3. INFILE 'ccb.dat'
   
4. DISCARDFILE 'ccb.dis'
   
5. append
   
6. INTO table t_bank_invoice
   
7. TRAILING NULLCOLS
   
8. (
   
9.    account_number "4367421823250099999",
   
10.    sub_number "00000",
    
11.    transac_date position(1:8)  DATE(8) "YYYYMMDD" TERMINATED BY ',',  
    
12.    transac_address   position(10)   char  TERMINATED BY ',',              
    
13.    out_amount decimal external  TERMINATED BY ','  nullif out_amount=blanks,
    
14.    in_amount decimal external  TERMINATED BY ',' nullif in_amount=blanks  ,
    
15.    balance decimal external  TERMINATED BY ',' nullif balance=blanks,
    
16.    opposite_number char  TERMINATED BY ',',     
    
17.    opposite_username char  TERMINATED BY ',',                                   
    
18.    currency char  TERMINATED BY ',',
    
19.    transac_comment  char  TERMINATED BY whitespace               
    
20. )
    
21. 
    
22. 
    

复制代码

全空行，控制文件中即使没有when也会产生dis文件。

1. LOAD DATA
   
2. characterset ZHS16GBK
   
3. INFILE 'icbc.dat'
   
4. DISCARDFILE 'icbc.dis'
   
5. append
   
6. INTO table t_bank_invoice
   
7. when (5) ='^'
   
8. FIELDS TERMINATED BY '^'
   
9. TRAILING NULLCOLS
   
10. (
    
11.    account_number "1402029101000999999",
    
12.    sub_number "00000",
    
13.    transac_date position(6:15) DATE(10) "YYYY-MM-DD" TERMINATED BY WHITESPACE,
    
14.    transac_address TERMINATED BY WHITESPACE
    
15.     "substr(:transac_address,2)",
    
16.    transac_comment TERMINATED BY WHITESPACE
    
17.     "substr(:transac_comment,2)",
    
18.    currency TERMINATED BY WHITESPACE
    
19.     "substr(:currency,2)",
    
20.    remit TERMINATED BY WHITESPACE
    
21.     "substr(:remit,2)",
    
22.    in_amount TERMINATED BY WHITESPACE
    
23.     "TO_NUMBER(substr(:in_amount,2),'999,999,999,999.00')",
    
24.    out_amount TERMINATED BY WHITESPACE
    
25.     "TO_NUMBER(substr(:out_amount,2),'999,999,999,999.00')",
    
26.    balance TERMINATED BY WHITESPACE
    
27.     "TO_NUMBER(substr(:balance,2),'999,999,999,999.00')",
    
28.    all_comment TERMINATED BY WHITESPACE
    
29.     "substr(:all_comment,2)"
    
30. )
    
31. 
    
32. 
    

复制代码

1. create directory dir2 as '/home/oracle/dir2';
   
2. 
   
3. grant read,write on directory dir1 to hr;
   
4. 
   
5. grant read, write on directory dir2 to hr;

复制代码

1. 
   
2. select  * from dba_external_tables;
   
3. 
   
4. CREATE TABLE hr.extab_employees
   
5.                   (employee_id       NUMBER(4),
   
6.                    first_name        VARCHAR2(20),
   
7.                     last_name         VARCHAR2(25),
   
8.                     hire_date         DATE)                  
   
9. ORGANIZATION EXTERNAL
   
10.      ( TYPE ORACLE_LOADER DEFAULT DIRECTORY dir1
    
11.        ACCESS PARAMETERS
    
12.        ( records delimited by newline
    
13.          badfile 'empxt%a_%p.bad'
    
14.          logfile 'empxt%a_%p.log'
    
15.          fields terminated by ','
    
16.          missing field values are null
    
17.      ( employee_id, first_name, last_name,
    
18.       hire_date char date_format date mask "dd-mon-yyyy"))
    
19.      LOCATION (dir1:'empxt1.dat', dir2:'empxt2.dat') )
    
20.      PARALLEL  REJECT LIMIT UNLIMITED;
    
21. 
    
22. 
    
23. select  * from dba_Tables t where t.owner='HR';

复制代码

/home/oracle/dir1:

[oracle@station79 dir1]$ cat empxt1.dat
3000,san,zhang,03-mar-2003

------------------------------------------------------------
[oracle@station79 dir2]$ cat empxt2.dat
4000,si,li,04-apr-2004
5000,wu,wang
6000,liu,ma,06-jun-2006
--------------------------------------------------

1. [oracle@station79 ~]$ sqlldr  hr/oracle_4U  control=tpasswd.ctl   external_table=GENERATE_ONLY  log=tpasswd.sql

复制代码