Oracle clob类型字段的插入问题

Question

在PL/SQL中执行下面的插入语句，其中XMLSTRING字段为clob类型，报错：ORA-01704: 字符串文字太长  clob类型最大不是支持4G吗？高手指点，小弟急用insert into TEST.AIRITEMBEANS (ORGCITY, DSTCITY, TAKEOFFDATE, XMLSTRING, AIRID) values ('aaa', 'bbb', '2010-01-02', '<?xml version="1.0" encoding="GB2312"?>
<av_response xmlns="http://xml.cwip.web.et.travelsky.com">
  <wsAirItemBeans>
    <wsAirItemBean>
      <index>0</index>
      <orgCity>PEK</orgCity>
      <dstCity>HAK</dstCity>
      <takeoffDateTime>2010-10-30 08:05</takeoffDateTime>
      <arrivalDateTime>2010-10-30 11:45</arrivalDateTime>
      <wsSegments>
        <wsSegmentBean>
          <airline>HU</airline>
          <fltNo>7382</fltNo>
          <org>PEK</org>
          <dst>HAK</dst>
          <takeoffDateTime>2010-10-30 08:05</takeoffDateTime>
          <arrivalDateTime>2010-10-30 11:45</arrivalDateTime>
          <planeStyle>330</planeStyle>
          <meal>true</meal>
          <eTicket>true</eTicket>
          <codeShare>false</codeShare>
          <isAsr>true</isAsr>
          <stop>0</stop>
          <wsProductCabins>
            <wsProductCabin>
              <agreementID>NORMAL</agreementID>
              <cabin>F</cabin>
              <cabinNum>6</cabinNum>
              <description>头等舱</description>
              <isSa>false</isSa>
              <fareIndex>1</fareIndex>
              <timestamp>1282206430407</timestamp>
              <signature>683ea700f84335ec1b1a7b5870daf931</signature>
            </wsProductCabin>
            <wsProductCabin>
              <agreementID>WO</agreementID>
              <cabin>Y</cabin>
              <cabinNum>A</cabinNum>
              <description>全价舱</description>
              <isSa>false</isSa>
              <fareIndex>2</fareIndex>
              <timestamp>1282206430407</timestamp>
              <signature>35492b3820ad821911bbb0a7a8703aa1</signature>
            </wsProductCabin>
          </wsProductCabins>
          <timestamp>1282206430408</timestamp>
          <signature>512f0180d499fc175cbff67b3c2c37b2</signature>
        </wsSegmentBean>
      </wsSegments>
    </wsAirItemBean>
    <wsAirItemBean>
      <index>1</index>
      <orgCity>PEK</orgCity>
      <dstCity>HAK</dstCity>
      <takeoffDateTime>2010-10-30 12:25</takeoffDateTime>
      <arrivalDateTime>2010-10-30 16:15</arrivalDateTime>
      <wsSegments>
        <wsSegmentBean>
          <airline>HU</airline>
          <fltNo>7182</fltNo>
          <org>PEK</org>
          <dst>HAK</dst>
          <takeoffDateTime>2010-10-30 12:25</takeoffDateTime>
          <arrivalDateTime>2010-10-30 16:15</arrivalDateTime>
          <planeStyle>738</planeStyle>
          <meal>true</meal>
          <eTicket>true</eTicket>
          <codeShare>false</codeShare>
          <isAsr>true</isAsr>
          <stop>0</stop>
          <wsProductCabins>
            <wsProductCabin>
              <agreementID>NORMAL</agreementID>
              <cabin>F</cabin>
              <cabinNum>6</cabinNum>
              <description>头等舱</description>
              <isSa>false</isSa>
              <fareIndex>3</fareIndex>
              <timestamp>1282206430408</timestamp>
              <signature>3fe663696dd8aaa9ee6122b03cb89ed9</signature>
            </wsProductCabin>
            <wsProductCabin>
              <agreementID>WO</agreementID>
              <cabin>Y</cabin>
              <cabinNum>A</cabinNum>
              <description>全价舱</description>
              <isSa>false</isSa>
              <fareIndex>4</fareIndex>
              <timestamp>1282206430408</timestamp>
              <signature>de693b6bf97bc6d43a7bb12bbe5586e7</signature>
            </wsProductCabin>
          </wsProductCabins>
          <timestamp>1282206430409</timestamp>
          <signature>5522b42d298926f604623622bca6d9a0</signature>
        </wsSegmentBean>
      </wsSegments>
    </wsAirItemBean>
    <wsAirItemBean>
      <index>2</index>
      <orgCity>PEK</orgCity>
      <dstCity>HAK</dstCity>
      <takeoffDateTime>2010-10-30 17:05</takeoffDateTime>
      <arrivalDateTime>2010-10-30 20:45</arrivalDateTime>
      <wsSegments>
        <wsSegmentBean>
          <airline>HU</airline>
          <fltNo>7082</fltNo>
          <org>PEK</org>
          <dst>HAK</dst>
          <takeoffDateTime>2010-10-30 17:05</takeoffDateTime>
          <arrivalDateTime>2010-10-30 20:45</arrivalDateTime>
          <planeStyle>738</planeStyle>
          <meal>true</meal>
          <eTicket>true</eTicket>
          <codeShare>false</codeShare>
          <isAsr>true</isAsr>
          <stop>0</stop>
          <wsProductCabins>
            <wsProductCabin>
              <agreementID>NORMAL</agreementID>
              <cabin>F</cabin>
              <cabinNum>6</cabinNum>
              <description>头等舱</description>
              <isSa>false</isSa>
              <fareIndex>5</fareIndex>
              <timestamp>1282206430409</timestamp>
              <signature>5774852657f58ea243f31426b7e07ce5</signature>
            </wsProductCabin>
            <wsProductCabin>
              <agreementID>WO</agreementID>
              <cabin>Y</cabin>
              <cabinNum>A</cabinNum>
              <description>全价舱</description>
              <isSa>false</isSa>
              <fareIndex>6</fareIndex>
              <timestamp>1282206430409</timestamp>
              <signature>facc2959fb683cf9295b171e367a04b7</signature>
            </wsProductCabin>
          </wsProductCabins>
          <timestamp>1282206430410</timestamp>
          <signature>e8133e6318c85db447227b0d157e5613</signature>
        </wsSegmentBean>
      </wsSegments>
    </wsAirItemBean>
    <wsAirItemBean>
      <index>3</index>
      <orgCity>PEK</orgCity>
      <dstCity>HAK</dstCity>
      <takeoffDateTime>2010-10-30 21:25</takeoffDateTime>
      <arrivalDateTime>2010-10-31 01:10</arrivalDateTime>
      <wsSegments>
        <wsSegmentBean>
          <airline>HU</airline>
          <fltNo>7282</fltNo>
          <org>PEK</org>
          <dst>HAK</dst>
          <takeoffDateTime>2010-10-30 21:25</takeoffDateTime>
          <arrivalDateTime>2010-10-31 01:10</arrivalDateTime>
          <planeStyle>738</planeStyle>
          <meal>true</meal>
          <eTicket>true</eTicket>
          <codeShare>false</codeShare>
          <isAsr>true</isAsr>
          <stop>0</stop>
          <wsProductCabins>
            <wsProductCabin>
              <agreementID>NORMAL</agreementID>
              <cabin>F</cabin>
              <cabinNum>6</cabinNum>
              <description>头等舱</description>
              <isSa>false</isSa>
              <fareIndex>7</fareIndex>
              <timestamp>1282206430410</timestamp>
              <signature>f8713159558a8e3a4447959c8e62ba4d</signature>
            </wsProductCabin>
            <wsProductCabin>
              <agreementID>WO</agreementID>
              <cabin>Y</cabin>
              <cabinNum>A</cabinNum>
              <description>全价舱</description>
              <isSa>false</isSa>
              <fareIndex>8</fareIndex>
              <timestamp>1282206430410</timestamp>
              <signature>3d7a2eeb80551daa67aee17a51a4c3b5</signature>
            </wsProductCabin>
          </wsProductCabins>
          <timestamp>1282206430411</timestamp>
          <signature>86964808525c659df9f125570c5e792e</signature>
        </wsSegmentBean>
      </wsSegments>
    </wsAirItemBean>
  </wsAirItemBeans>
  <wsReturnAirItemBeans>
    <wsAirItemBean>
      <index>0</index>
      <orgCity>HAK</orgCity>
      <dstCity>PEK</dstCity>
      <takeoffDateTime>2010-11-30 08:00</takeoffDateTime>
      <arrivalDateTime>2010-11-30 11:30</arrivalDateTime>
      <wsSegments>
        <wsSegmentBean>
          <airline>HU</airline>
          <fltNo>7181</fltNo>
          <org>HAK</org>
          <dst>PEK</dst>
          <takeoffDateTime>2010-11-30 08:00</takeoffDateTime>
          <arrivalDateTime>2010-11-30 11:30</arrivalDateTime>
          <planeStyle>738</planeStyle>
          <meal>true</meal>
          <eTicket>true</eTicket>
          <codeShare>false</codeShare>
          <isAsr>true</isAsr>
          <stop>0</stop>
          <wsProductCabins>
            <wsProductCabin>
              <agreementID>NORMAL</agreementID>
              <cabin>F</cabin>
              <cabinNum>6</cabinNum>
              <description>头等舱</description>
              <isSa>false</isSa>
              <fareIndex>9</fareIndex>
              <timestamp>1282206430411</timestamp>
              <signature>8bd183f4ada73e81ecf0c3ab9bf8d86c</signature>
            </wsProductCabin>
            <wsProductCabin>
              <agreementID>WO</agreementID>
              <cabin>Y</cabin>
              <cabinNum>A</cabinNum>
              <description>全价舱</description>
              <isSa>false</isSa>
              <fareIndex>10</fareIndex>
              <timestamp>1282206430412</timestamp>
              <signature>bcfb6b6971c97baf619dc4b798c1478a</signature>
            </wsProductCabin>
          </wsProductCabins>
          <timestamp>1282206430412</timestamp>
          <signature>42a9c44953db016b61e127bc093b7644</signature>
        </wsSegmentBean>
      </wsSegments>
    </wsAirItemBean>
    <wsAirItemBean>
      <index>1</index>
      <orgCity>HAK</orgCity>
      <dstCity>PEK</dstCity>
      <takeoffDateTime>2010-11-30 12:45</takeoffDateTime>
      <arrivalDateTime>2010-11-30 16:15</arrivalDateTime>
      <wsSegments>
       
    .........
', seq_test.nextval);

Answer 1

sql语句是不能超过4k长度的.
截取循环拼进去.

Answer 2

SQL> create table test_clob
  2  (id number(2),
  3   content clob);
 
Table created
 
SQL> 
SQL> create or replace procedure proc_test_clob
  2  as
  3   v_content varchar2(4000);
  4  begin
  5   v_content:='1234567890';
  6   insert into test_clob(id,content) values(1,empty_clob());
  7   for i in 1..length(v_content)
  8   loop
  9    update test_clob t set t.content=t.content||substr(v_content,i,1) where id=1;
 10   end loop;
 11  end;
 12  /
 
Procedure created
 
SQL> execute proc_test_clob;
 
PL/SQL procedure successfully completed
 
SQL> select * from test_clob;
 
 ID CONTENT
--- --------------------------------------------------------------------------------
  1 1234567890
 
SQL>