Skip to Main Content
  • Questions
  • Loading a file in External Table with a fields more than 4000 characters

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, W.K..

Asked: March 06, 2017 - 11:58 am UTC

Last updated: April 18, 2017 - 9:57 am UTC

Version: Oracle 11g

Viewed 1000+ times

You Asked

Hi Tom,

I have a file with the following structure:
+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=
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*****
+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=


I want to load data from this file into an Oracle table with External Table. Everything is going fine until the third lines. At the last field, the external table rejects it. Probably cause this field has linefeed in de data and number of characters is above 4000.

I have also tried it with CLOB, but it also rejects the third line. Both attempts have been failed, How can I solve this problem?

Regards,

Wai Tsang

+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+'=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=
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;    
+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=






and Chris said...

So, you're running into issues when reading the lines that don't start with a date (LOAD SUMMARY, etc.)?

If so, the problem isn't that they're CLOBs. It's that Oracle Database handles each as a separate row. So there isn't enough colons per line to match the columns you've define. You can get around this by using the "missing field values are null" clause:

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 


If you want everything in the "LOAD SUMMARY" section to be one row, you need to have a different delimiter from newline.

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here