We have a table which has one XMLType column & we are trying to load data into this table using SQL*LDR.
DDL -
CREATE TABLE schema.TABLE1
(COL1 CHAR(10 CHAR) NOT NULL
,COL2 CHAR(23 CHAR) NOT NULL
,COL_XMLTYPE XMLTYPE NOT NULL
, CONSTRAINT CN751 PRIMARY KEY (COL1))
TABLESPACE TS_AA;
CTL File –
OPTIONS (DIRECT=TRUE)
LOAD DATA
INFILE '../data/TABLE1.dat'
BADFILE '../data/TABLE1.bad'
TRUNCATE
INTO TABLE TABLE1
XMLTYPE(COL_XMLTYPE)
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
COL1 CHAR(10),
COL2 CHAR(23) ,
filename FILLER CHAR(120),
COL_XMLTYPE LOBFILE(filename) TERMINATED BY EOF
)
DAT file –
"0000000558","2010-09-07T11:33:09.800","/home/oracle/Load_Data/data/IG57MT99.XML"
"0000006669","2010-09-07T11:33:10.210","/home/oracle/Load_Data/data/IG57MT99.XML"
"0000003330","2010-09-07T11:33:10.270","/home/oracle/Load_Data/data/IG57MT99.XML"
"0000055551","2010-09-07T11:33:10.350","/home/oracle/Load_Data/data/IG57MT99.XML"
Case 1-
When we execute the above combination as-is, we get error - SQL*Loader-588: XMLTYPE directive not allowed for table TABLE1
We modified the DDL to -
CREATE TABLE schema.TABLE1
(COL1 CHAR(10 CHAR) NOT NULL
, COL2 CHAR(23 CHAR) NOT NULL
,COL_XMLTYPE XMLTYPE NOT NULL
, CONSTRAINT CN751 PRIMARY KEY (COL1))
XMLType COLUMN COL_XMLTYPE STORE AS CLOB
(TABLESPACE TS_AA)
But again same error - SQL*Loader-588: XMLTYPE directive not allowed for table TABLE1
Case 2-
We remove the command - XMLTYPE(COL_XMLTYPE) from CTL file and run again
Record 1: Rejected - Error on table TABLE1, column COL_XMLTYPE.
ORA-39830: XML parsing failed
LPX-00231: invalid character 76 ('L') found in a Name or Nmtoken
Question –
Which is the correct DDL for XMLTYPE column tables
What is the correct CTL file configuration for XMLTYPE column tables
Do you have any insights on how to resolve the errors in case 1 & 2