oracle 在oracle中连接CLOB数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13477130/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
concatenate CLOB data in oracle
提问by user1836818
I need to concatenate various CLOB data into a single CLOB field.
我需要将各种 CLOB 数据连接到一个 CLOB 字段中。
I have a database with various rows, each containing a few char fields (BATCH_ID for example), and a CLOB (BATCH_BODY). I am unsure as to how to concatenate the CLOBs in order to make everything a single row.
我有一个包含各种行的数据库，每行包含几个字符字段（例如 BATCH_ID）和一个 CLOB（BATCH_BODY）。我不确定如何连接 CLOB 以使所有内容都成为一行。
the end goal is to group together all rows in which the BATCH_ID is the same into a single row with a single CLOB file that is made up of various BATCH_BODY's
最终目标是将 BATCH_ID 相同的所有行组合到一个行中，并使用由各种 BATCH_BODY 组成的单个 CLOB 文件
回答by Kieren Johnstone
CONCAT works with CLOBs it seems:
CONCAT 似乎与 CLOB 一起工作：
http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions026.htm
http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions026.htm
回答by Dave Friedman
To work with CLOBs longer than 32K without truncation, I needed code like this:
要在不截断的情况下处理超过 32K 的 CLOB，我需要这样的代码：
--ALL_XML := ALL_XML || SINGLE_XML;
--The above crashed on Oracle 11g with text beyond 32K, so...
lobLength := DBMS_LOB.GETLENGTH(SINGLE_XML);
FOR i IN 0..100 LOOP
offset := i * buffSize + 1;
EXIT WHEN SINGLE_XML IS NULL OR lobLength < offset;
-- fill the buffer with final data chunk to append to the lob
buffer := DBMS_LOB.SUBSTR(SINGLE_XML, buffSize, offset);
DBMS_LOB.APPEND(ALL_XML, TO_CLOB(buffer));
END LOOP;
