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