时间戳

首页 > 解决方案 > 为什么 SQL 加载器/外部表不能使用空值

sql - 为什么 SQL 加载器/外部表不能使用空值

问题描述

下面的代码一直在工作，但由于我们已经从 12c 升级到 19c，它就停止了。other_name 和birth_date 部分出错了。csv 中的 Other_name 主要为空白，因此应上传 null。当它现在为空时，它会导致birth_date出错（在数据文件ORA-01858的第2行中获取错误处理列BIRTH_DATE：在需要数字的地方找到了非数字字符）

如果我自己手动修改文件以将某些内容添加到 other_name 中，那么它仍然可以工作。

任何人以前见过这样的问题，并且知道如何解决？

    ALTER SESSION SET CURRENT_SCHEMA = dbo;

--
-- Product Category File 
--

DROP TABLE mis_product_category_ext;

CREATE TABLE mis_product_category_ext
                         (
                           PACKAGE_LABEL     varchar2(4),
                           PACKAGE_DESCR     varchar2(64),
                           PACKAGE_CAT       varchar2(10)
                        )
           ORGANIZATION EXTERNAL 
           ( 
             TYPE ORACLE_LOADER 
             DEFAULT DIRECTORY SEPA_FILES
             ACCESS PARAMETERS 
             ( 
               records delimited by newline 
               badfile SEPA_FILES:'mis_product_category_ext.bad'
               logfile SEPA_FILES:'mis_product_category_ext.log'
               skip 1
               fields terminated by ','
               missing field values are null 
               ( 
                package_label,package_descr,package_cat
               )
             ) 
             LOCATION ('product_category.csv') 
           ) 
           REJECT LIMIT UNLIMITED; 
           
declare
--
   v_insurer varchar2(10);
--
begin

--
-- Incoming data files from insurers
--

   for i in 1..3 loop
   --
      case i
         when 1 then v_insurer := 'aviva';
         when 2 then v_insurer := 'glohealth';
         when 3 then v_insurer := 'vhi';
      end case;
   --
      for i in (select table_name from all_tables where lower(table_name) = 'mis_mem_verify_ext_'||v_insurer||'') loop
         execute immediate 'DROP TABLE mis_mem_verify_ext_'||v_insurer||'';
      end loop;
   --
      execute immediate
      'CREATE TABLE mis_mem_verify_ext_'||v_insurer||'
                         (
                           REQ_INSURERS_PRIMARY_KEY         varchar2(100),
                           UNIQUE_REFERENCE_ID_1            varchar2(100),
                           NAME_OF_LAST_INSURER             varchar2(10),
                           FIRSTNAME                        varchar2(60),
                           SURNAME                          varchar2(60),
                           OTHER_NAME                       varchar2(60),
                           BIRTH_DATE                       date,
                           FIRST_LINE_OF_ADDRESS            varchar2(60),
                           COUNTY                           varchar2(30),
                           UNIQUE_REFERENCE_ID_2            varchar2(15),
                           PRODUCT_TYPE                     varchar2(15),
                           PLAN_NAME                        varchar2(100),
                           START_DATE_OF_POLICY             date,
                           END_DATE_OF_POLICY               date,
                           MEM_MATCH                        varchar2(1),
                           CREDITED_PERIOD                  number(4),
                           UNEMPLOYMENT_PERIOD              number(4),
                           LCR_LOADING                      number,
                           LOADING_OVERRIDDEN               varchar2(1)
                        )
           ORGANIZATION EXTERNAL 
           ( 
             TYPE ORACLE_LOADER 
             DEFAULT DIRECTORY SEPA_FILES
             ACCESS PARAMETERS 
             ( 
               records delimited by newline 
               badfile SEPA_FILES:''mem_verify_'||v_insurer||'_incoming.bad''
               logfile SEPA_FILES:''mem_verify_'||v_insurer||'_incoming.log''
               skip 1
               fields terminated by 0X''09''
               LRTRIM
               missing field values are null 
               ( req_insurers_primary_key,unique_reference_id_1,name_of_last_insurer,firstname,
                 surname,other_name,birth_date char date_format date mask "yyyy-mm-dd",first_line_of_address,
                 county,unique_reference_id_2,product_type,plan_name,start_date_of_policy char date_format date mask "yyyy-mm-dd",
                 end_date_of_policy char date_format date mask "yyyy-mm-dd",mem_match,credited_period,unemployment_period,
                 lcr_loading,loading_overridden
               )
             ) 
             LOCATION (''lcr_mem_verification_'||v_insurer||'_incoming.csv'') 
           ) 
           PARALLEL 
           REJECT LIMIT UNLIMITED'; 
   --
   end loop;
--
end;
/

spool off;

exit

标签： sqloraclesql-loader

解决方案

删除 OPTIONALLY ENCLOSED BY '"' 行 从控制文件。

SQL*Loader 将空白/制表符视为空白。一旦它找到空白，当前字段就在其后终止。跳过下一个非空白字符之前的所有空白，因此将跳过空列。

  1. 跳过连续定界符的原因是包含了“OPTIONALLY ENCLOSED BY '”'的封装规范</li>

如果附件是可选的，则该字符不需要与附件字符匹配，但如果它是强制性的，则该字符必须匹配，否则该行将被拒绝。

  1. 将字段指定为字段子句的一部分以及未出现在列子句中的字段。

  2. 您也可以尝试将分隔符从逗号更改为其他内容。

推荐阅读