尝试将Clob加载到Oracle表时，加载了太多记录 [英] When trying to load a clob to an Oracle table, loading too many records

问题描述

我有一个.xml文件，试图将其加载到Oracle表的clob字段中. .xml文件的长度为49行.当我运行sqlloader cntl时，整个.xml文件将作为新行加载到我的表中，共49次.我正在使用Oracle 11.2.0.3我在做什么错了?

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);

我的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>

推荐答案

带有LOB的Oracle Loader的典型设置为:

The typical setup for Oracle Loader with LOBs would be:

• 带有加载说明的控制文件
• 有一个带有元数据(作者，日期，文件名)的主数据文件.文件中的每一行都成为目标表中的一行.
• 主文件中每个条目都有一个单独的文件，该文件被加载到目标表的CLOB或BLOB列中.

在您的情况下，XML文件似乎既用作主数据文件又用作单独的LOB文件(尽管我不太清楚为什么；您还在命令行上指定XML吗?).

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?).

因此，您需要正确指定这三件事.为了避免出现三个文件，您可以使用BEGINDATA指令将主数据文件放入控制文件中(这是INFILE之后的内容):

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

这篇关于尝试将Clob加载到Oracle表时，加载了太多记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！