Oracle数据类型-CLOB

需求说明：

报表中有一个字段，是根据主键从多条记录聚合而来的（也就是行转列）。

但因为Oracle数据库SQL中Varchar2最大长度为4000字节，当聚合结果大于4000字节时，query运行会报错（ORA-06502: PL/SQL: numeric or value error: character string buffer too small）。经DBA指点，准备将聚合后的结果作为CLOB返回。

所以，算是第一次使用CLOB数据类型吧。

用户实现该聚合功能的自定义函数如下：

CREATE OR REPLACE FUNCTION CONCATENATE_LIST_CLOB (P_CURSOR IN SYS_REFCURSOR)
   RETURN CLOB
IS
   L_RETURN   CLOB;
   L_TEMP     CLOB;
BEGIN
   LOOP
      FETCH P_CURSOR INTO L_TEMP;

      EXIT WHEN P_CURSOR%NOTFOUND;
      L_RETURN := L_RETURN || ', ' || L_TEMP;
   END LOOP;

   RETURN LTRIM (L_RETURN, ', ');
END;

 

--------------------------------------------------------------------------------------------------------------------------------------------

CLOB Data Type
The CLOB data type stores single-byte and multibyte character data. Both fixed-width
and variable-width character sets are supported, and both use the database character
set. CLOB objects can store up to (4 gigabytes -1) * (the value of the CHUNK parameter of
LOB storage) of character data. If the tablespaces in your database are of standard
block size, and if you have used the default value of the CHUNK parameter of LOB
storage when creating a LOB column, then this is equivalent to (4 gigabytes - 1) *
(database block size).
CLOB objects have full transactional support. Changes made through SQL, the DBMS_
LOB package, or Oracle Call Interface (OCI) participate fully in the transaction. CLOB
value manipulations can be committed and rolled back. However, you cannot save a
CLOB locator in a PL/SQL or OCI variable in one transaction and then use it in another
transaction or session.