如何从oracle中较大的clob中获取多达5MB的文本/字符串/数据

嗨，Oracle，

 CREATE TABLE XMLISSUE
                ( 
                  xmltablecolumn clob
                 
                );
复制

创建表格

如下图所示，我已经向clob插入了一些数据

DECLARE 
 XMLCLOB clob;

BEGIN
 
   SELECT  XMLTYPE(
                   DBMS_XMLGEN.GETXML(q'{SELECT * FROM   whse_detail e WHERE  ROWNUM <= 10000 }')
                  ).getclobval()   as data1  INTO XMLCLOB 
          FROM   DUAL; 
          
         -- SELECT XMLCLOB FROM DUAL;
        --  DBMS_OUTPUT.PUT_LINE(XMLCLOB);
        INSERT INTO XMLISSUE(XMLTABLECOLUMN) VALUES(XMLCLOB);
          
 -- SELECT LENGTHB(XMLTABLECOLUMN) AS AASDF
--FROM XMLISSUE;
 
END;
复制

 SELECT XMLTABLECOLUMN AS xmlclob
FROM XMLISSUE;
复制

XMLISSUE是我的表有clob类型列。该列有超过10 MB的文本/数据。
我需要从该clob获取少于5MB的数据。
我该怎么做 ..？？

提前谢谢你 ..

您可以使用DBMS_LOB中的函数来查找clob的长度并对其进行子字符串:

drop table t purge;
create table t (
  x clob
);

declare
  tmp clob;
  str varchar2(32767) := lpad('x', 32767, 'x');
begin
  dbms_lob.createtemporary(tmp,true);
  
  for i in 1 .. 200 loop
    dbms_lob.writeappend(tmp,length(str),str);
  end loop;
  
  insert into t values (tmp);
end;
/

select dbms_lob.getlength(x) from t;

DBMS_LOB.GETLENGTH(X)  
6,553,400  

select dbms_lob.getlength(dbms_lob.substr(x, 4000, 1)) from t;

DBMS_LOB.GETLENGTH(DBMS_LOB.SUBSTR(X,4000,1))  
4,000  
复制

http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_lob.htm#ARPLS600

但是这种方法有问题。Dbms_lob.substr返回一个varchar2。在SQL中，此长度的最大为4,000字节 *。所以，如果你试图返回比这更长的字符串，你会得到一个错误:

select dbms_lob.substr(x, 4001, 1) from t;

SQL Error: ORA-06502: PL/SQL: numeric or value error: character string buffer too small
复制

PL/SQL中varchar2的限制为32k。所以你可以提取更大的字符串，但仍然不是你正在寻找的5Mb:

declare
  subs varchar2(32767);
  tclob clob;
begin
  select x
  into   tclob
  from   t;
  
  subs := dbms_lob.substr(tclob, 32767, 1);
      
  dbms_output.put_line('Clob length: ' || dbms_lob.getlength(subs));
end;
/

PL/SQL procedure successfully completed.
Clob length: 32767

declare
  subs  clob;
  tclob clob;
begin
  select x
  into   tclob
  from   t;

  subs := dbms_lob.substr(tclob, 32768, 1);
      
  dbms_output.put_line('Clob length: ' || dbms_lob.getlength(subs));
end;
/

PL/SQL procedure successfully completed.
Clob length:
复制

那么如何解决这个问题呢？

重复调用PL/SQL中的dbms_lob.Substr!

declare
  len   pls_integer;
  tclob clob;
  subc  clob;
begin

  select x
  into   tclob
  from   t;
  
  for i in 0 .. 99 loop
    subc := subc || dbms_lob.substr(tclob, 32767, (32767*i) +1);
  end loop;
  
  dbms_output.put_line('Clob length: ' || dbms_lob.getlength(subc));
end;
/

PL/SQL procedure successfully completed.
Clob length: 3276700
复制

注意: 处理clob时，函数的作用是字符数。如果你确定字符串只使用单字节字符，这很好。但是，如果您的文本可以包含多字节字符，则获取5*1024 ^ 2的子字符串可能会超过5Mb。如果5Mb是一个硬限制，您可以将clob转换为blob。然后substr对字节数起作用。请注意: 如果最后一个字符是多字节的，则可能会错误地将其切成两个!

* 在12.1中，如果您使用的是扩展数据类型，则会增加到32,767。