ulcase4.ctl的改写:
- LOAD DATA
- INFILE "ulcase4.dat"
- DISCARDFILE "ulcase4.dis"
- DISCARDMAX 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) date(9) "DD-MON-RR" )
复制代码 直接路径加载之后,尤其要看日志。
ulcase7可以用一些sql函数。如果要导入的文本文件里头,有超出英文的字符,要确认“码表”。
- LOAD DATA
- characterset ZHS16GBK
- INFILE 'ccb.dat'
- DISCARDFILE 'ccb.dis'
- append
- INTO table t_bank_invoice
- TRAILING NULLCOLS
- (
- account_number "4367421823250099999",
- sub_number "00000",
- transac_date position(1:8) DATE(8) "YYYYMMDD" TERMINATED BY ',',
- transac_address position(10) char TERMINATED BY ',',
- out_amount decimal external TERMINATED BY ',' nullif out_amount=blanks,
- in_amount decimal external TERMINATED BY ',' nullif in_amount=blanks ,
- balance decimal external TERMINATED BY ',' nullif balance=blanks,
- opposite_number char TERMINATED BY ',',
- opposite_username char TERMINATED BY ',',
- currency char TERMINATED BY ',',
- transac_comment char TERMINATED BY whitespace
- )
复制代码 全空行,控制文件中即使没有when也会产生dis文件。
- LOAD DATA
- characterset ZHS16GBK
- INFILE 'icbc.dat'
- DISCARDFILE 'icbc.dis'
- append
- INTO table t_bank_invoice
- when (5) ='^'
- FIELDS TERMINATED BY '^'
- TRAILING NULLCOLS
- (
- account_number "1402029101000999999",
- sub_number "00000",
- transac_date position(6:15) DATE(10) "YYYY-MM-DD" TERMINATED BY WHITESPACE,
- transac_address TERMINATED BY WHITESPACE
- "substr(:transac_address,2)",
- transac_comment TERMINATED BY WHITESPACE
- "substr(:transac_comment,2)",
- currency TERMINATED BY WHITESPACE
- "substr(:currency,2)",
- remit TERMINATED BY WHITESPACE
- "substr(:remit,2)",
- in_amount TERMINATED BY WHITESPACE
- "TO_NUMBER(substr(:in_amount,2),'999,999,999,999.00')",
- out_amount TERMINATED BY WHITESPACE
- "TO_NUMBER(substr(:out_amount,2),'999,999,999,999.00')",
- balance TERMINATED BY WHITESPACE
- "TO_NUMBER(substr(:balance,2),'999,999,999,999.00')",
- all_comment TERMINATED BY WHITESPACE
- "substr(:all_comment,2)"
- )
复制代码- create directory dir2 as '/home/oracle/dir2';
-
- grant read,write on directory dir1 to hr;
-
- grant read, write on directory dir2 to hr;
复制代码
- select * from dba_external_tables;
- CREATE TABLE hr.extab_employees
- (employee_id NUMBER(4),
- first_name VARCHAR2(20),
- last_name VARCHAR2(25),
- hire_date DATE)
- ORGANIZATION EXTERNAL
- ( TYPE ORACLE_LOADER DEFAULT DIRECTORY dir1
- ACCESS PARAMETERS
- ( records delimited by newline
- badfile 'empxt%a_%p.bad'
- logfile 'empxt%a_%p.log'
- fields terminated by ','
- missing field values are null
- ( employee_id, first_name, last_name,
- hire_date char date_format date mask "dd-mon-yyyy"))
- LOCATION (dir1:'empxt1.dat', dir2:'empxt2.dat') )
- PARALLEL REJECT LIMIT UNLIMITED;
- 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
--------------------------------------------------- [oracle@station79 ~]$ sqlldr hr/oracle_4U control=tpasswd.ctl external_table=GENERATE_ONLY log=tpasswd.sql
复制代码
|