Hi ,
We need to load a file which has seven columns (second and third one is a xml file and other columns are small string values) into a table (xml should be loaded as is into clob column). We have created an external table something like below
CREATE TABLE TEST1
(
COL1 INTEGER,
COl2 CLOB,
col3 CLOB,
col4 VARCHAR2(50),
col5 VARCHAR2(50),
col6 VARCHAR2(50)
col47 VARCHAR2(50)
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY EXT_DATA
ACCESS PARAMETERS
( RECORDS DELIMITED BY NEWLINE
BADFILE EXT_BAD:'TEST_%a_%p.bad'
LOGFILE EXT_LOG:'TEST.log'
NODISCARDFILE
FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"'
MISSING FIELD VALUES ARE NULL
(
COL1 INTEGER ,
COL2 VARCHAR(32767) ,
COL3 VARCHAR(32767),
col4,
col5,
col6,
col7 )
)
LOCATION (EXT_DATA:'TEST.dat')
)
REJECT LIMIT UNLIMITED
PARALLEL ( DEGREE DEFAULT INSTANCES DEFAULT );
File :
1|"<Fixture><FixtureIdentifierFormula /><SectionIdentifierFormula>PK&Key(Planogram)</SectionIdentifierFormula><Sequence>0</Sequence><Adjacency><Left><DBKey>17909</DBKey><FixtureIdentifier /></Left><Right><DBKey>17831</DBKey><FixtureIdentifier /></Right></Adjacency><SectionAssoc><Section><DBKey>2791165</DBKey><SectionIdentifier>PK333785</SectionIdentifier><X>-222.000</X><Width>30.000</Width></Section></SectionAssoc></Fixture>"|ABC|NULL|NULL|NULL|NULL
When we query the table I am getting something like below for clob column . As you see first few characters missing and last columns are also coming into first clob column. Please note that even in the file and even in the table it is single line.
xture><FixtureIdentifierFormula /><SectionIdentifierFormula>PK&Key(Planogram)</SectionIdentifierFormula><Sequence>0</Sequence><Adjacency><Left><DBKey>17909</DBKey><FixtureIdentifier /></Left><Right><DBKey>17831</DBKey><FixtureIdentifier /></Right></Adjacency><SectionAssoc><Section><DBKey>2791165</DBKey><SectionIdentifier>PK333785</SectionIdentifier><X>-222.000</X><Width>30.000</Width></Section></SectionAssoc></Fixture>"|ABC|NULL|NULL|NULL|NULL
Can anyone help how to fix this. Thanks in advance