SQL*Loader In Direct Path Corrupts The Binary XMLTYPE

(Doc ID 1447304.1)

Last updated on NOVEMBER 28, 2016

Applies to:

Oracle Server - Enterprise Edition - Version 11.2.0.2 to 11.2.0.3 [Release 11.2]
Information in this document applies to any platform.

Symptoms

You load XML documents containing specific characters into a table using binary XMLTYPE. After the load, the select statement shows corrupt data. Please follow the next example that shows this:

-- create the environment
connect / as sysdba

create user rmsuser identified by rmsuser default tablespace users temporary
tablespace temp;
grant connect, resource to rmsuser;
alter user rmsuser quota unlimited on users;

connect rmsuser/rmsuser

-- create table with binary XMLTYPE (default)
create table test_xml1
(
   id number,
   text_v varchar2(100),
   text_x sys.xmltype
);

-- create table with XMLTYPE stored as CLOB
create table test_xml2
(
   id number,
   text_v varchar2(100),
   text_x sys.xmltype
)
xmltype text_x store as clob;

-- control file (ldr.ctl)
load data
infile *
truncate
into table test_xml1
--into table test_xml2
fields terminated by ';'
trailing nullcols
(
   id,
   text_v,
   filnam filler,
   text_x lobfile (filnam) terminated by eof
)
begindata
1;äöüÄÖÜß;test.xml

-- XML document test.xml
<?xml version="1.0" encoding="ISO-8859-15" standalone="yes"?>
<a_tag>äöüÄÖÜß</a_tag>


Start SQL*Loader with:

#> sqlldr rmsuser/rmsuser control=ldr.ctl direct=true


Now change the control file to load the table TEST_XMLS (with XMLTYPE stored as CLOB) and restart SQL*Loader. Then select the tables with:

set pages 1000 lines 80 long 10000

select * from test_xml1;

ID
----------
TEXT_V
--------------------------------------------------------------------------------
TEXT_X
--------------------------------------------------------------------------------
         1
äöüÄÖÜß
<?xml version="1.0" encoding="ISO-8859-15" standalone='yes'?>
<a_tag>äöüÃ
          ÃÃÃâ¬</a_tag>  <-- Corrupt value


select * from test_xml2;

ID
----------
TEXT_V
--------------------------------------------------------------------------------
TEXT_X
--------------------------------------------------------------------------------
         1
äöüÄÖÜß
<?xml version="1.0" encoding="ISO-8859-15" standalone="yes"?>
<a_tag>äöüÄÖÜߤ</a_tag>

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms