Oracle 在具有超过4000个字符的字段的外部表中加载文件

嗨，汤姆，

我有一个具有以下结构的文件:

+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=
2017-03-01 16:17:08 : INFO : (65193 | WRITER_1_*_1) : (IS | SYSTEM 123) : testnode01_abc_12 : WRT_8167 : Start loading table [table_x] at: Wed Mar 01 16:17:08 2017
2017-03-01 16:17:08 : INFO : (65193 | WRITER_1_*_1) : (IS | SYSTEM 123) : testnode01_abc_12 : WRT_8168 : End loading table [table_x] at: Wed Mar 01 16:17:08 2017
2017-03-01 16:17:08 : INFO : (65193 | WRITER_1_*_1) : (IS | SYSTEM 123) : testnode01_abc_12 : WRT_8035 : Load complete time: Wed Mar 01 16:17:08 2017

LOAD SUMMARY
============

WRT_8036 Target: TMP_F_BASISPROCES_ID_ONDERZOEK (Instance Name: [TMP_F_BASISPROCES_ID_ONDERZOEK])
WRT_8044 No data loaded for this target


WRT_8036 Target: D_ID_OND_ACTIVITEIT_GROEP (Instance Name: [D_ID_OND_ACTIVITEIT_GROEP])
WRT_8044 No data loaded for this target



2017-03-01 16:17:08 : INFO : (65193 | WRITER_1_*_1) : (IS | SYSTEM 123) : testnode01_abc_12 : WRT_8043 : *****END LOAD SESSION*****
+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=

我想将数据从此文件加载到具有外部表的Oracle表中。一切都很好，直到第三行。在最后一个字段，外部表拒绝它。可能导致此字段在de数据中有换行符，字符数高于4000。

我也用CLOB尝试过，但它也拒绝了第三行。两次尝试都失败了，请问该如何解决？

问候，

曾伟

+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+'=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=
drop table  inf_logs                        
-- attempt 1                         
                         
CREATE TABLE inf_logs (
  TEXT01 VARCHAR2(4000)  
, TEXT02 VARCHAR2(4000)  
, TEXT03 VARCHAR2(4000)
, TEXT04 VARCHAR2(4000)
, TEXT05 VARCHAR2(4000)
, TEXT06 VARCHAR2(4000)
, TEXT07 VARCHAR2(4000)
)
ORGANIZATION EXTERNAL (
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY DIRLOGS
  ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE 
    FIELDS TERMINATED BY ' : ' 
    )
  LOCATION ('file_log.TXT')
)
PARALLEL 5
REJECT LIMIT UNLIMITED;        

+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=
CREATE TABLE informatica_logs2 (
  TEXT01 VARCHAR2(4000)  
, TEXT02 VARCHAR2(4000)  
, TEXT03 VARCHAR2(4000)
, TEXT04 VARCHAR2(4000)
, TEXT05 VARCHAR2(4000)
, TEXT06 VARCHAR2(4000)
, TEXT07 CLOB
)
ORGANIZATION EXTERNAL (
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY DIRLOGS
  ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE
    FIELDS TERMINATED BY ' : ' 
    NOTRIM 
          ( 
            TEXT01 CHAR,
            TEXT02 CHAR,
            TEXT03 CHAR,
            TEXT04 CHAR,
            TEXT05 CHAR,
            TEXT06 CHAR,
            TEXT07 CHAR(10000) 
          ) 
    )
  LOCATION ('file_log.TXT')
)
PARALLEL 5
REJECT LIMIT UNLIMITED;    
+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=

因此，在阅读不以日期开头的行 (加载摘要等) 时，您遇到了问题？

如果是这样，问题不在于它们是CLOBs。这是Oracle数据库将每个处理为单独的行。因此，每行没有足够的冒号来匹配您定义的列。您可以通过使用 “缺少字段值为null” 子句来解决此问题:

CREATE TABLE inf_logs (
  TEXT01 VARCHAR2(4000)  
, TEXT02 VARCHAR2(4000)  
, TEXT03 VARCHAR2(4000)
, TEXT04 VARCHAR2(4000)
, TEXT05 VARCHAR2(4000)
, TEXT06 VARCHAR2(4000)
, TEXT07 VARCHAR2(4000)
)
ORGANIZATION EXTERNAL (
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY tmp
  ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE 
    FIELDS TERMINATED BY ' : ' 
    missing field values are null
    )
  LOCATION ('test.txt')
)
PARALLEL 5
REJECT LIMIT UNLIMITED;   

select * from inf_logs;

TEXT01                                                                                             TEXT02  TEXT03                  TEXT04             TEXT05             TEXT06    TEXT07                                                      
2017-03-01 16:17:08                                                                                INFO    (65193 | WRITER_1_*_1)  (IS | SYSTEM 123)  testnode01_abc_12  WRT_8167  Start loading table [table_x] at: Wed Mar 01 16:17:08 2017  
2017-03-01 16:17:08                                                                                INFO    (65193 | WRITER_1_*_1)  (IS | SYSTEM 123)  testnode01_abc_12  WRT_8168  End loading table [table_x] at: Wed Mar 01 16:17:08 2017    
2017-03-01 16:17:08                                                                                INFO    (65193 | WRITER_1_*_1)  (IS | SYSTEM 123)  testnode01_abc_12  WRT_8035  Load complete time: Wed Mar 01 16:17:08 2017                
                                                                                                                                                                                                                                               
LOAD SUMMARY                                                                                                                                                                                                                                   
============                                                                                                                                                                                                                                   
                                                                                                                                                                                                                                               
WRT_8036 Target: TMP_F_BASISPROCES_ID_ONDERZOEK (Instance Name: [TMP_F_BASISPROCES_ID_ONDERZOEK])                                                                                                                                              
WRT_8044 No data loaded for this target                                                                                                                                                                                                        
                                                                                                                                                                                                                                               
                                                                                                                                                                                                                                               
WRT_8036 Target: D_ID_OND_ACTIVITEIT_GROEP (Instance Name: [D_ID_OND_ACTIVITEIT_GROEP])                                                                                                                                                        
WRT_8044 No data loaded for this target                                                                                                                                                                                                        
                                                                                                                                                                                                                                               
                                                                                                                                                                                                                                               
                                                                                                                                                                                                                                               
2017-03-01 16:17:08                                                                                INFO    (65193 | WRITER_1_*_1)  (IS | SYSTEM 123)  testnode01_abc_12  WRT_8043  *****END LOAD SESSION*****                                  

 17 rows selected 

如果您希望 “加载摘要” 部分中的所有内容都是一行，则需要具有与newline不同的分隔符。