Home » RDBMS Server » Server Utilities » SQLLDR - error loading xml column into xmltype column (SQL*Loader Oracle 11.2.0.1.0 - 64 bit)
SQLLDR - error loading xml column into xmltype column [message #647422] Wed, 27 January 2016 08:36 Go to next message
thomasaf27
Messages: 4
Registered: January 2016
Junior Member
Hi All, I have found slightly similar issues as mine within this forum, but none of the suggested resolutions work.
I'm having an issue running SQL*Loader. The oracle table I'm loading is a normal table, but has one column with data type XMLTYPE.


My table "ff_email_xml_archive" is defined as follows:

i_event VARCHAR2(15) NOT NULL
i_cust VARCHAR2(20) NOT NULL
i_kit_id VARCHAR2(4) NOT NULL
i_kit_ltr VARCHAR2(3) NOT NULL
c_mail VARCHAR2(1)
i_agnt VARCHAR2(12) NOT NULL
xml_string XMLTYPE


My control file:
load data
INFILE 'c:\ff_email_xml_archive'
INTO TABLE ff_email_xml_archive
TRUNCATE
FIELDS TERMINATED BY '|' optionally enclosed by "'"
(i_event,
i_cust,
i_kit_id,
i_kit_ltr,
c_mail,
i_agnt,
file_name filler CHAR(8000),
xml_string lobfile (file_name) terminated by eof)


My input file ff_email_xml_archive (just 1 record):
'201401110939391'|'10057997'|'OHPC'|'012'|'E'|'aaabdn'|'<?xml version= 1.0 encoding= ISO-8859-1 ?>< Kit_Info> < Kit_ID>OHPC</Kit_ID> < Ltr_Cd>012</Ltr_Cd><Kit_ID_Ltr>OHPC-012</Kit_ID_Ltr> < Rate_Quote_FRM></Rate_Quote_FRM> <App_FRM></App_FRM> <Life_FRM></Life_FRM> < Dental_FRM></Dental_FRM><Event_Id>201401110999777</Event_Id> <Lang>EN</Lang> < Med_Quest>00000</Med_Quest> < Effective_Dt>03/01/2014</Effective_Dt> <Mrkt_Seg></Mrkt_Seg> < Prep_Dt>2014-01-11</Prep_Dt> <Num_Apps/> <Rating_State>OH</Rating_State> <Contract_Type/> < Contract_Level>0.000</Contract_Level><Cust_Info> <Cust_ID>777777</Cust_ID> <Name_Prfx></Name_Prfx> < First_Name>Sandra</First_Name> <Mid_Init></Mid_Init> <Last_Nm>Goings</Last_Nm> <DOB>05/29/1965</DOB> <Gender>F</Gender> <SSN/> < Phone>313-330-3030</Phone><Cust_Email>tester47@gmail.com</Cust_Email> <Age>48</Age> <H_Level>1.000</H_Level> <Event_Id/> </Cust_Info> <Adrs_Info> <Adrs_1>119 San Eaton< /Adrs_1><Adrs_2></Adrs'|

Error:
value used for ROWS parameter changed from 64 to 26
SQL*Loader-503: Error appending extension to file (<?xml version= 1.0 encoding= ISO-8859-1 ?> <Kit_Info> <Kit_ID>OHPC</Kit_ID> <Ltr_Cd>012</Ltr_Cd><Kit_ID_Ltr>

SQL*Loader-567: unable to derive file name
SQL*Loader-509: System error: The operation completed successfully.
SQL*Loader-2026: the load was aborted because SQL Loader cannot continue.

Why am I getting this error? I've tried changing the data type for field xml_string in the table to CLOB, tried changing the "file_name" in the control file to CHAR(100) and CHAR(4000).

Please let me know if I have not posted this in the correct discussion forum. Thanks.

[Updated on: Wed, 27 January 2016 09:04]

Report message to a moderator

Re: SQLLDR - error loading xml column into xmltype column [message #647423 is a reply to message #647422] Wed, 27 January 2016 09:36 Go to previous messageGo to next message
Michel Cadot
Messages: 67223
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

'<?xml version= 1.0 encoding= ISO-8859-1 ?>...' is obviously not a file name.

Re: SQLLDR - error loading xml column into xmltype column [message #647425 is a reply to message #647423] Wed, 27 January 2016 10:12 Go to previous messageGo to next message
thomasaf27
Messages: 4
Registered: January 2016
Junior Member
Your are correct. That is the xml that I literally want to put into a column (xml_string) that is defined as data type xmltype in the oracle table. So you're saying I shouldn't be using the "file_name filler CHAR(8000)" in my control card.

I've tried defining the column in the oracle table as CLOB also, but get a different error (below).

Record 1: Rejected - Error on table FF_EMAIL_XML_ARCHIVE, column XML_STRING.
Field in data file exceeds maximum length

How else can this work?

Sorry, I'm a novice at this. I'm searching for answers on forums like this. Please pardon my obvious mistakes.

[Updated on: Wed, 27 January 2016 10:20]

Report message to a moderator

Re: SQLLDR - error loading xml column into xmltype column [message #647426 is a reply to message #647425] Wed, 27 January 2016 10:42 Go to previous messageGo to next message
Michel Cadot
Messages: 67223
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I don't know much more but I can read the documentation. Smile
In Database Utilities, Chapter 11 Loading Objects, LOBs, and Collections, Section Loading LOBs, it is said:

Quote:
XML columns are columns declared to be of type SYS.XMLTYPE. SQL*Loader treats XML columns as if they were CLOBs. All of the methods described in the following sections for loading LOB data from the primary data file or from LOBFILEs are applicable to loading XML columns.


So just read and apply the following sections, there are examples. Smile

[Updated on: Wed, 27 January 2016 10:42]

Report message to a moderator

Re: SQLLDR - error loading xml column into xmltype column [message #647441 is a reply to message #647422] Wed, 27 January 2016 15:51 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
The XML data that you have provided is invalid. It is missing some double quotes in the first line, there are spaces after some of the < that are not allowed, and the end of the XML is missing. In the following example, I have corrected your XML, then demonstrated how to load it. I also used dat as an extension for the file name.

-- corrected data:
SCOTT@orcl> HOST TYPE ff_email_xml_archive.dat
'201401110939391'|'10057997'|'OHPC'|'012'|'E'|'aaabdn'|'<?xml version= "1.0" encoding= "ISO-8859-1"?><Kit_Info> <Kit_ID>OHPC</Kit_ID> <Ltr_Cd>012</Ltr_Cd><Kit_ID_Ltr>OHPC-012</Kit_ID_Ltr> <Rate_Quote_FRM></Rate_Quote_FRM> <App_FRM></App_FRM> <Life_FRM></Life_FRM> <Dental_FRM></Dental_FRM><Event_Id>201401110999777</Event_Id> <Lang>EN</Lang> <Med_Quest>00000</Med_Quest> <Effective_Dt>03/01/2014</Effective_Dt> <Mrkt_Seg></Mrkt_Seg> <Prep_Dt>2014-01-11</Prep_Dt> <Num_Apps/> <Rating_State>OH</Rating_State> <Contract_Type/> <Contract_Level>0.000</Contract_Level><Cust_Info> <Cust_ID>777777</Cust_ID> <Name_Prfx></Name_Prfx> <First_Name>Sandra</First_Name> <Mid_Init></Mid_Init> <Last_Nm>Goings</Last_Nm> <DOB>05/29/1965</DOB> <Gender>F</Gender> <SSN/> <Phone>313-330-3030</Phone><Cust_Email>tester47@gmail.com</Cust_Email> <Age>48</Age> <H_Level>1.000</H_Level> <Event_Id/> </Cust_Info> <Adrs_Info> <Adrs_1>119 San Eaton</Adrs_1><Adrs_2></Adrs_2></Adrs_Info></Kit_Info>'|


-- control file:
SCOTT@orcl> HOST TYPE test.ctl
load data
INFILE ff_email_xml_archive.dat
INTO TABLE ff_email_xml_archive
TRUNCATE
FIELDS TERMINATED BY '|' optionally enclosed by "'"
(i_event,
i_cust,
i_kit_id,
i_kit_ltr,
c_mail,
i_agnt,
xml_string CHAR(5000))


-- table:
SCOTT@orcl> CREATE TABLE ff_email_xml_archive
  2    (i_event     VARCHAR2(15) NOT NULL,
  3     i_cust      VARCHAR2(20) NOT NULL,
  4     i_kit_id    VARCHAR2(4) NOT NULL,
  5     i_kit_ltr   VARCHAR2(3) NOT NULL,
  6     c_mail      VARCHAR2(1),
  7     i_agnt      VARCHAR2(12) NOT NULL,
  8     xml_string  XMLTYPE)
  9  /

Table created.


-- load:
SCOTT@orcl> HOST SQLLDR scott/tiger CONTROL=test.ctl LOG=test.log

SQL*Loader: Release 12.1.0.2.0 - Production on Wed Jan 27 13:44:56 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Path used:      Conventional
Commit point reached - logical record count 1

Table FF_EMAIL_XML_ARCHIVE:
  1 Row successfully loaded.

Check the log file:
  test.log
for more information about the load.


-- results:
SCOTT@orcl> SELECT * FROM ff_email_xml_archive
  2  /

I_EVENT         I_CUST               I_KI I_K C I_AGNT
--------------- -------------------- ---- --- - ------------
XML_STRING
--------------------------------------------------------------------------------
201401110939391 10057997             OHPC 012 E aaabdn
<?xml version="1.0" encoding="WINDOWS-1252"?>
<Kit_Info>
  <Kit_ID>OHPC</Kit_ID>
  <Ltr_Cd>012</Ltr_Cd>
  <Kit_ID_Ltr>OHPC-012</Kit_ID_Ltr>
  <Rate_Quote_FRM/>
  <App_FRM/>
  <Life_FRM/>
  <Dental_FRM/>
  <Event_Id>201401110999777</Event_Id>
  <Lang>EN</Lang>
  <Med_Quest>00000</Med_Quest>
  <Effective_Dt>03/01/2014</Effective_Dt>
  <Mrkt_Seg/>
  <Prep_Dt>2014-01-11</Prep_Dt>
  <Num_Apps/>
  <Rating_State>OH</Rating_State>
  <Contract_Type/>
  <Contract_Level>0.000</Contract_Level>
  <Cust_Info>
    <Cust_ID>777777</Cust_ID>
    <Name_Prfx/>
    <First_Name>Sandra</First_Name>
    <Mid_Init/>
    <Last_Nm>Goings</Last_Nm>
    <DOB>05/29/1965</DOB>
    <Gender>F</Gender>
    <SSN/>
    <Phone>313-330-3030</Phone>
    <Cust_Email>tester47@gmail.com</Cust_Email>
    <Age>48</Age>
    <H_Level>1.000</H_Level>
    <Event_Id/>
  </Cust_Info>
  <Adrs_Info>
    <Adrs_1>119 San Eaton</Adrs_1>
    <Adrs_2/>
  </Adrs_Info>
</Kit_Info>


1 row selected.


Re: SQLLDR - error loading xml column into xmltype column [message #647444 is a reply to message #647441] Wed, 27 January 2016 21:36 Go to previous messageGo to next message
thomasaf27
Messages: 4
Registered: January 2016
Junior Member
Thanks Barbara, that helped. I was able to get this working using your example. I did see in the documentation that the xml must be valid"....

I'm literally exporting the xml out of an Informix relational database table into a file as is, using a SQL statement. I'm then using SQLLDR to load the data into oracle tables. I can see that when I'm exporting the data, the xml is getting changed.

Thanks very much.
Re: SQLLDR - error loading xml column into xmltype column [message #647445 is a reply to message #647444] Wed, 27 January 2016 22:09 Go to previous messageGo to next message
BlackSwan
Messages: 26719
Registered: January 2009
Location: SoCal
Senior Member
Any modern browser can & will open a properly formatted XML data file, so it should be trivial to validate before using it with SQLLDR.
Re: SQLLDR - error loading xml column into xmltype column [message #647916 is a reply to message #647445] Thu, 11 February 2016 09:04 Go to previous messageGo to next message
thomasaf27
Messages: 4
Registered: January 2016
Junior Member
Just to follow up on this thread one last time with my final findings....

As I mentioned, I was extracting data from an Informix database and reloading it into an Oracle database using SQL*Loader. What I found out is that when I extracted the data from the Informix database, the column in the Informix database was defined as TEXT data type and it put a back slash and new line character after every line in the XML (from the TEXT column).

So the data exported from the Informix database for the column with xml looked like this...

201401110939391 10057997 OHPC 012 E aaabdn
<?xml version="1.0" encoding="WINDOWS-1252"?>
<Kit_Info>\
<Kit_ID>OHPC</Kit_ID>\
<Ltr_Cd>012</Ltr_Cd>\
<Kit_ID_Ltr>OHPC-012</Kit_ID_Ltr>\
<Rate_Quote_FRM/>\
.......

So when I tried loading this xml into the Oracle table (where the column that will hold the xml data was defined as XMLType), it failed.

The initial solution I employed was to remove all of the new line characters (got this idea from one of the replies to this thread). But I then ran a Perl/Sed script to remove the back slash and new line characters from the data file before loading it into Oracle with SQL*Loader. That allowed me to load this column into the oracle table without any solution.

Just wanted to put my final solution in because this forum has proven to be so helpful and useful to folks like me....and someone may have this exact issue that I had. This helped me to better understand SQL*Loader.

Thanks again to all who replied.
Re: SQLLDR - error loading xml column into xmltype column [message #647918 is a reply to message #647916] Thu, 11 February 2016 09:44 Go to previous message
Michel Cadot
Messages: 67223
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And we thank you for your feedback.

Previous Topic: if i import database using impdp full import its also change character set and nls_lang
Next Topic: Data Pump throwing errors
Goto Forum:
  


Current Time: Thu Jul 02 17:04:42 CDT 2020