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

来源：互联网发布：js div style 编辑：程序博客网时间：2024/05/16 15:35

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

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

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

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

sqlldr 命令就是调用 SQL*Loader 的实用程序, 它会根据 .ctl 控制文件定义的规则有选择的导入 .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 dataappendinto table inv.MTL_TRANSACTIONS_INTERFACEwhen (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

0 0