SQL*Loader 并发程序

Executable：ITEM_PRICE_UPT

Short Name：ITEM_PRICE_UPT

Application：XX

Description：

Execution Method：SQL*Loader

Execution File Name：ITEM_PRICE_UPT

Subroutine Name：

Execution File Path：

---

Excel 文件：PUR_PRICE_UPT.csv

FTP上传路径： /u01/oraclep/prodappl/xx/11.5.0/edi/inbound

列：

ORGANIZATION

SEGMENT1

LIST_PRICE_PER_UNIT

 

---

FTP_PATH:   $CUX_TOP/bin

 

file name:  ITEM_PRICE_UPT.ctl

 

代码如下：
---- file name:     ITEM_PRICE_UPT.ctl
---- created by:    BenioGuo
---- created date:  26-SEP-2011


options (skip=1,errors=1000000)
LOAD DATA
Append
INTO TABLE inv.ascp_inv_item_temp
fields terminated by "," optionally enclosed by '"'  
TRAILING NULLCOLS
    (ORGANIZATION_CODE "UPPER(TRIM(:ORGANIZATION_CODE))",
  segment1 "UPPER(TRIM(:SEGMENT1))",
    LIST_PRICE_PER_UNIT    "REPLACE(:LIST_PRICE_PER_UNIT,CHR(13))")

 

1.options 

skip跳过的行数，这里1是指跳过第一行表头。

errors -- 允许的错误记录数，可以用他来控制一条记录都不能错

2. Append 在表后面附加行

3. fields terminated by ","  字段以逗号分隔

4.trailing nullcols 

记录中没有内容（空格、空白、或null）的列被当作null

---

 PLSQL提交请求：

    v_request_id := fnd_request.submit_request(p_app_mod,--application short name
                                               p_func_name,--executable name，eg. (ITEM_PRICE_UPT)
                                               NULL,
                                               SYSDATE,
                                               FALSE,
                                               p_file_path,--csv path
                                               chr(0));

 

 

---

表结构

 

create table INV.ASCP_INV_ITEM_TEMP
(
  organization_code             VARCHAR2(3),
  segment1                      VARCHAR2(40),

  list_price_per_unit           NUMBER

);

 

---