When trying to load a clob to an Oracle table, loa

Question

I have an .xml file that I am trying to load into a clob field in an Oracle table. The .xml file is 49 lines in length. When I run my sqlloader cntl, the entire .xml file gets loaded into my table as a new row, 49 times. I am using Oracle 11.2.0.3 What am I doing wrong?

CREATE TABLE "LEAD_REPORTING_CLOB" 
("SHARED_XML" CLOB);

my cntl:

LOAD DATA
INFILE * 
REPLACE

INTO TABLE LEAD_REPORTING_CLOB
TRAILING NULLCOLS
(
SHARED_XML        LOBFILE(CONSTANT '/export/RFD/Lead_Reports/LEADRPT.xml') TERMINATED BY EOF
)


xml file:

<?xml version="1.0" encoding="utf-8"?>
<LeadReport xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://www.xxx.yyy/efile">
  <Lead>
    <SubmissionId>530153999999zdhxxx</SubmissionId>
    <PatternDescription>OLF</PatternDescription>
    <SourceIPAddress>
      <IPAddress>
        <IPv4AddressTxt>999.99.99.999</IPv4AddressTxt>
      </IPAddress>
      <TCPPortNumber>443</TCPPortNumber>
      <IPTs>2016-02-05T13:16:55-05:00</IPTs>
    </SourceIPAddress>
    <SourceDeviceId>
      <DeviceId>74B3A80AE5C99X9X99999C17XX59E5XX3779999A</DeviceId>
      <DeviceIdTs>2016-02-05T13:16:55-05:00</DeviceIdTs>
    </SourceDeviceId>
    <AdditionalInformation>
      <ReturnSubmStatus>A</ReturnSubmStatus>
     <EFIN>555555</EFIN>
      <EmailAddressDomain>GMAIL.COM</EmailAddressDomain>
      <PhoneNumberLastFour>1899</PhoneNumberLastFour>
    </AdditionalInformation>
  </Lead>
  <Lead>
    <SubmissionId>9999992016108floxxx2</SubmissionId>
    <AssociatedState>
      <StateSubmissionId>9999992016108xhaxxx2</StateSubmissionId>
      <StateOrCityCode>GAST</StateOrCityCode>
    </AssociatedState>
    <PatternDescription>OLF</PatternDescription>
    <SourceIPAddress>
      <IPAddress>
        <IPv4AddressTxt>188.88.8.188</IPv4AddressTxt>
      </IPAddress>
      <TCPPortNumber>443</TCPPortNumber>
      <IPTs>2016-04-17T08:07:54-04:00</IPTs>
    </SourceIPAddress>
    <SourceDeviceId>
      <DeviceId>6B9D4C4B0159XXX8FA30391BC8A099999009B366</DeviceId>
      <DeviceIdTs>2016-04-17T08:07:54-04:00</DeviceIdTs>
    </SourceDeviceId>
    <AdditionalInformation>
      <ReturnSubmStatus>A</ReturnSubmStatus>
      <EFIN>444444</EFIN>
      <EmailAddressDomain>YAHOO.COM</EmailAddressDomain>
      <PhoneNumberLastFour>3822</PhoneNumberLastFour>
    </AdditionalInformation>
  </Lead>
</LeadReport>

Answer 1

The typical setup for Oracle Loader with LOBs would be:

• The control file with the loading instructions
• There is a main datafile with the meta data (author, date, filename). Each line in the file becomes a row in the target table.
• There is a separate file for each entry in the main file, which is loaded into the CLOB or BLOB column of the target table.

In your case, the XML files seems to be used as both the main data file and the separate LOB file (though I don't fully understand why; are you also specifying the XML on the command line?).

So you need to specify these three things properly. In order to avoid three files, you put the main data file into the control file using the BEGINDATA directive (this is what the astrisk after INFILE is for):

LOAD DATA
INFILE * 
REPLACE

INTO TABLE LEAD_REPORTING_CLOB
FIELDS TERMINATED BY ',' TRAILING NULLCOLS
(
    FILENAME,
    SHARED_XML  LOBFILE(FILENAME) TERMINATED BY EOF
)

BEGINDATA
/export/RFD/Lead_Reports/LEADRPT.xml