Search code examples
oraclesql-loader

sqlldr: Getting 'ORA-19032: Expected XML tag, got no content' for null values

I'm trying to load a table with oracle sqlldr but get ORA-19032: Expected XML tag, got no content on (allowed!) null fields.

If the table is populated by inserts, or the column is updated to null, everthing is fine. But loading via sqlldr doesn't work.

Loader call:

sqlldr $DBCS control=$TABLE.ctl data=$TABLE.csv bad=$LOGDIR/$TABLE.bad log=$LOGDIR/$TABLE.log rows=10000 bindsize=20000000 readsize=20000000 silent=header,feedback

CTL-File:

LOAD DATA
INFILE MY_TABLE.csv "STR '|\n'"
INTO TABLE MY_TABLE APPEND
FIELDS TERMINATED BY ';'
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
  "NORMAL_COLUMN1" CHAR(40)
, "NORMAL_COLUMN2" CHAR(250)
, "NORMAL_COLUMN3" CHAR(250)
, "XML_COLUMN" CHAR(16000)
)

Table Definition:

CREATE TABLE MY_TABLE
(
  NORMAL_COLUMN1        NUMBER(14) not null,
  NORMAL_COLUMN2        VARCHAR2(250) not null,
  NORMAL_COLUMN3        VARCHAR2(250),
  XML_COLUMN            SYS.XMLTYPE    
);

csv line - not working:

21001;"lulul";"lalal";|

csv line - working:

21001;"lulul";;"<a>ala</a>"|

Note: In general, i'd create "correct" datatypes for the "normal columns", but the ctl-file is created by an unload-script...and it works ;)

Important: It works for non-null values for the xml column.

I hope there is a way to modify the corresponding line in the ctl-File, but i didn't find a solution yet.

Solution

  • The issue appears to be that at some point within that loader execution something similar to the following is occurring where the NULL value is being passed as a parameter to the XmlType(value) constructor or the createXML(value) function:

    select XmlType('') as xml_value from dual;

    I have tried a few ways to work around it with no success. I can continue to research it when I have more time, but I wanted to at least post this as an explanation. You could (hackily) work around this by doing something like: 

    LOAD DATA
INFILE MY_TABLE.csv "STR '|\n'"
INTO TABLE MY_TABLE APPEND
FIELDS TERMINATED BY ';'
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
  "NORMAL_COLUMN1" CHAR(40)
, "NORMAL_COLUMN2" CHAR(250)
, "NORMAL_COLUMN3" CHAR(250)
, "Nvl(XML_COLUMN, '<empty />')" CHAR(16000)
)

    Not the ideal solution, but it should get you moving forward. Let me know if you end up just going this route, otherwise I will see if I can find something better.