跳过将数据加载到外部表中的列

Question

这是我创建的外部表：

CREATE TABLE emp_load 
(
  "ESN_NO" VARCHAR2 (200), 
  "MAKER" VARCHAR2 (30), 
  "HANDSET" VARCHAR2 (20), 
  "MODEL_NO" VARCHAR2 (20), 
  "OMH_TTSL_FLAG" VARCHAR2 (10), 
  "OFFER" VARCHAR2 (100), 
  "STATUS" VARCHAR2 (20), 
  "STATUS_UPDATED_DATE" DATE
) ORGANIZATION EXTERNAL (
  TYPE ORACLE_LOADER DEFAULT DIRECTORY "EXT_TAB_DIR" ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE skip 1
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    missing FIELD VALUES are NULL
    ) LOCATION ('BAU.csv')
) ;                         

BAU.csv文件的示例内容

ESN No          Make    Model   RI_value
A10000428A5348  Lava    C180    OFFER14
A10000428A5349  Lava    C180    OFFER14
A10000428A534A  Lava    C180    OFFER14
A10000428A534B  Lava    C180    OFFER14

它工作得很好，但是数据没有出现在我需要的列中；我希望列handset是空的，但是当前文件中的model值被加载到handset列中。所以我现在得到：

ESN_NO          MAKER  HANDSET  MODEL_NO  OMH_TTSL_FLAG  OFFER  STATUS  STATUS_UPDATED_DATE  
A10000428A5348  Lava   C180     OFFER14                                                      
A10000428A5349  Lava   C180     OFFER14                                                      
A10000428A534A  Lava   C180     OFFER14                                                      
A10000428A534B  Lava   C180     OFFER14                                                      

我希望跳过handset列，在model_no中插入model；跳过omh_ttsl_flag，在offer中插入文件中的ri_value。所以我最后想做的是：

ESN_NO          MAKER  HANDSET  MODEL_NO  OMH_TTSL_FLAG  OFFER    STATUS  STATUS_UPDATED_DATE  
A10000428A5348  Lava            C180                     OFFER14                               
A10000428A5349  Lava            C180                     OFFER14                               
A10000428A534A  Lava            C180                     OFFER14                               
A10000428A534B  Lava            C180                     OFFER14                               

如何在将数据加载到外部表时跳过这些列？

Answer 1

为此，可以将文件中的字段指定为fields子句的一部分，并指定不与 clause一起出现的字段，例如：

COLUMN TRANSFORMS ("HANDSET" FROM NULL, "OMH_TTSL_FLAG" FROM NULL)

所以你的声明是：

CREATE TABLE emp_load 
(
  "ESN_NO" VARCHAR2 (200), 
  "MAKER" VARCHAR2 (30), 
  "HANDSET" VARCHAR2 (20), 
  "MODEL_NO" VARCHAR2 (20), 
  "OMH_TTSL_FLAG" VARCHAR2 (10), 
  "OFFER" VARCHAR2 (100), 
  "STATUS" VARCHAR2 (20), 
  "STATUS_UPDATED_DATE" DATE
)
ORGANIZATION EXTERNAL
(
  TYPE ORACLE_LOADER DEFAULT DIRECTORY "EXT_TAB_DIR" ACCESS PARAMETERS
  (
    RECORDS DELIMITED BY NEWLINE skip 1
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    MISSING FIELD VALUES ARE NULL
    (
      "ESN_NO" CHAR (200), 
      "MAKER" CHAR (30), 
      "MODEL_NO" CHAR (20), 
      "OFFER" CHAR (100)
    )
    COLUMN TRANSFORMS
    (
      "HANDSET" FROM NULL,
      "OMH_TTSL_FLAG" FROM NULL,
      "STATUS" FROM NULL,
      "STATUS_UPDATED_DATE" FROM NULL
    )
  )
  LOCATION ('BAU.csv')
) ;  

我还包括了另外两列，这不是必要的，但有助于记录您希望看到的内容。

将示例数据转换为CSV，将其加载为：

set sqlformat ansiconsole
column esn_no format a20
select * from emp_load;

ESN_NO          MAKER  HANDSET  MODEL_NO  OMH_TTSL_FLAG  OFFER    STATUS  STATUS_UPDATED_DATE  
A10000428A5348  Lava            C180                     OFFER14                               
A10000428A5349  Lava            C180                     OFFER14                               
A10000428A534A  Lava            C180                     OFFER14                               
A10000428A534B  Lava            C180                     OFFER14                               

Answer 2

insert into emp_load (ESN_NO,MAKER,MODEL_NO,OFFER)
values ('A10000428A5348','Lava','Lava','OFFER14)

默认值为空，因此所有其他列都将收到空。