SQLLDR - error loading xml column into xmltype column [message #647422] |
Wed, 27 January 2016 08:36  |
 |
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 #647425 is a reply to message #647423] |
Wed, 27 January 2016 10:12   |
 |
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   |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
I don't know much more but I can read the documentation. 
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. 
[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   |
 |
Barbara Boehmer
Messages: 9104 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.
|
|
|
|
|
|
|