How to Import XML data into Oracle database table using sqlldr command

Question

I am trying to import XML data into Oracle database, but getting the error described bellow

Created the table:

CREATE TABLE fv_xml_12 (
   id    NUMBER,
   fv  XMLTYPE)
   XMLTYPE fv STORE AS OBJECT RELATIONAL
      XMLSCHEMA "http://www.example.com/fvInteger.xsd" 
      ELEMENT "FeatureVector";

Created the loader:

options (errors=9999999, rows=5)
load data
 characterset WE8MSWIN1252
infile '/home/alucard/fv_data_cophir/loader_pbf/semiestruturado/12dim/FeatureVectors_XML.txt'
 badfile '/home/alucard/fv_data_cophir/loader_pbf/semiestruturado/12dim/FeatureVectors.bad'
 discardfile '/home/alucard/fv_data_cophir/loader_pbf/semiestruturado/12dim/FeatureVectors.dsc'
 into table fv_xml_12
 fields terminated by " "
( id, fv)

The data:

1 <FeatureVector><feature>11</feature><feature>50</feature><feature>19</feature><feature>27</feature><feature>8</feature><feature>13</feature><feature>17</feature><feature>22</feature><feature>25</feature><feature>9</feature><feature>9</feature><feature>20</feature></FeatureVector>
2 <FeatureVector><feature>13</feature><feature>34</feature><feature>31</feature><feature>7</feature><feature>18</feature><feature>8</feature><feature>20</feature><feature>15</feature><feature>12</feature><feature>10</feature><feature>19</feature><feature>19</feature></FeatureVector>

sqlldr command:

sqlldr user/pass control=imp_fv_12dim.ctl

But I am getting the errors...

Record 1: Rejected - Error on table FV_XML_12, column FV. Field in data file exceeds maximum length Record 2: Rejected - Error on table FV_XML_12, column FV. Field in data file exceeds maximum length

What is wrong?

Alex Poole Alex Poole · Accepted Answer · 2017-12-06T20:01:11

You aren't specifying a data type for the fv field, so it defaults to CHAR; and from the documentation:

For a delimited CHAR field for which no length is specified, the default is 255 bytes.

Your XML values are about 280 characters, so they exceed that limit. You can avoid the error by specifying a larger size:

...
fields terminated by " "
( id, fv char(4000) )

Tested in 11gR2; without the size I get the same error, with the size specified the two rows load successfully. (Although I created the table without the store as as I don't have your .xsd.) But it should work exactly the same in 12c.

Also see Load XMLType Data Using SQL*Loader for other approaches, such as putting each XML into a separate file.

How to Import XML data into Oracle database table using sqlldr command

1 Answers