SQL*Loader 从文本文件向数据库导入数据

之前我的一遍 blog 写了怎样使用 Toad 这个工具从 excel 或者 csv 文件向数据库导入数据. 其实 Oracle 自己提供了这样一个工具, 叫做 SQL*Loader. 这个工具使用起来没有那么直接, 也比较容易出错, 但是它非常适合导入大数据量的文本. 执行效率非常高, 号称一小时能导入100 G (听说).

这里给出一个简单的例子, 怎么去使用它.

比方说, 有个客户想要从他之前使用的 ERP 软件里面, 把数据导入到 Oracle 的 EBS. 当然他不可能直接表对表的复制过来, 毕竟两个软件的表结构不一样. 那么他就需要把数据导入到 EBS 的接口表里面. 现在他想要把数据导入到 INV 模块的接口表 MTI 里面. 这是他需要两个文件. 一个文件是 .dat 文件, 里面存放的是所有需要导入的数据, 这个文件可能非常大. 另一个是控制文件, .ctl 文件, 里面存放的是导入法则. 当这两个文件都准备好了, 运行下面的命令:

sqlldr apps/apps control=***.ctl data=***.dat

数据文件可能是这样的:

INVENTORY_RECEIPT_IFD         DCS INV-RCV   0000000000003791368058627                         WN                                                    OA133-1-141113      OA133-1-141113                     WLN                 AVAILABLE                     0000000018                                  EAWLG 000200010000000018                                        395       X0                                    0.00WMD120141113142153INTRANSIT                     FGI                           395                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  

load data
append
into table inv.MTL_TRANSACTIONS_INTERFACE
when (1:21 = 'INVENTORY_RECEIPT_IFD')
 and (35:41 = 'INV-RCV')
 and (372:372 = 'X')
   (TRANSACTION_INTERFACE_ID  "MTL_MATERIAL_TRANSACTIONS_S.nextval", 
    TRANSACTION_HEADER_ID      CONSTANT '0',
    CREATION_DATE              SYSDATE,
    CREATED_BY                 CONSTANT '1198', 
    LAST_UPDATE_DATE           SYSDATE,
    LAST_UPDATED_BY            CONSTANT '1198',
    SOURCE_CODE                CONSTANT 'WMS WI MRECL',
    SOURCE_LINE_ID             CONSTANT '0',	
    SOURCE_HEADER_ID           CONSTANT '0',	
    PROCESS_FLAG               CONSTANT '1',	
    TRANSACTION_MODE           CONSTANT '3',    
    TRANSACTION_TYPE_ID	       CONSTANT '2',    
    TRANSACTION_ACTION_ID      CONSTANT '27'  ,   
    TRANSACTION_SOURCE_TYPE_ID CONSTANT '13' ,   
    ORGANIZATION_ID          POSITION (492:501),	
    TRANSFER_ORGANIZATION    POSITION (362:371), 
    TRANSACTION_SOURCE_NAME  POSITION (95:104),  
    VENDOR_LOT_NUMBER        POSITION (129:148) 
                               "replace(:VENDOR_LOT_NUMBER, '-')",
    TRANSACTION_DATE         POSITION (418:431) DATE 'YYYYMMDDHH24MISS',
    TRANSACTION_QUANTITY     POSITION (254:263),  
    TRANSFER_SUBINVENTORY    POSITION (462:471),  
    SUBINVENTORY_CODE        POSITION (432:441),  
    LOC_SEGMENT1             POSITION (149:168)   
                  "substr(:LOC_SEGMENT1, 1,instr (:LOC_SEGMENT1 , '-')-1) ",
    ITEM_SEGMENT1            POSITION (65:94),    
    TRANSACTION_UOM          POSITION (298:299)
                               NULLIF (TRANSACTION_UOM = BLANKS),
    ATTRIBUTE1               POSITION (300:303),  
    ATTRIBUTE3               POSITION (125:128),   
    SHIPMENT_NUMBER          POSITION (169:203),  
    TRANSACTION_REFERENCE    POSITION (45:64)      
                              "ltrim(:transaction_reference,'0')"
    )

它是使用字符位置去获取数据文件中的数据的.

如果不通过字符位置, 那么在数据文件中就需要分隔符, 一般使用逗号. csv 文件就是典型的使用逗号作为分隔符的文件, 所以非常适合作为数据文件的格式.

写控制文件是一个非常复杂的事情, 如果像上面的例子那样使用字符位置去定位, 非常容易出错. Oracle 提供了一个脚本, 可以自动产生控制文件. 可以参考 Note 1019523.6

set echo off
set heading off
set verify off
set feedback off
set show off
set trim off
set pages 0
set concat on
set lines 300
set trimspool on
set trimout on

spool &1..ctl

select 'LOAD DATA'||chr (10)||
       'INFILE '''||lower (table_name)||'.dat'''||chr (10)||
       'INTO TABLE '||table_name||chr (10)||
       'FIELDS TERMINATED BY '','''||chr (10)||
       'TRAILING NULLCOLS'||chr (10)||'('
from   all_tables
where  table_name = upper ('&1');

select decode (rownum, 1, ' ', ' , ')||
       rpad (column_name, 33, ' ')||
       decode (data_type, 'VARCHAR2', 'CHAR NULLIF ('||column_name||'=BLANKS)',
                          'FLOAT', 'DECIMAL EXTERNAL NULLIF('||column_name||'=BLANKS)',
                          'NUMBER', decode (data_precision, 0, 'INTEGER EXTERNAL NULLIF ('||column_name||'=BLANKS)',
                                    decode (data_scale, 0, 'INTEGER EXTERNAL NULLIF ('||column_name||'=BLANKS)',
                           'DECIMAL EXTERNAL NULLIF ('||column_name||'=BLANKS)')),
                           'DATE', 'DATE "MM/DD/YY" NULLIF ('||column_name||'=BLANKS)', null)
from   user_tab_columns
where  table_name = upper ('&1')
order  by column_id;

select ')'
from   sys.dual;
spool  off