以前处理LOB字段都是使用DBMS_LOB包，最近看了文档才发现，原来很多常用的字符函数都是支持LOB字段的。

建立一个测试表：

SQL> CREATE TABLE T_LOB (ID NUMBER, CONTENTS CLOB);

Table created.

SQL> DECLARE
  2     V_LOB CLOB;
  3  BEGIN
  4        INSERT INTO T_LOB
  5     VALUES (1, EMPTY_CLOB())
  6     RETURN  CONTENTS INTO V_LOB;
  7     FOR I IN 1..100 LOOP
  8             DBMS_LOB.WRITEAPPEND(V_LOB, 32767, LPAD('A', 32767, 'A'));
  9     END LOOP;
 10  END;
 11  /

PL/SQL procedure successfully completed.

SQL> SELECT ID, DBMS_LOB.GETLENGTH(CONTENTS) FROM T_LOB;

        ID DBMS_LOB.GETLENGTH(CONTENTS)
---------- ----------------------------
         1                      3276700

SQL> SELECT ID, LENGTH(CONTENTS) FROM T_LOB;

        ID LENGTH(CONTENTS)
---------- ----------------
         1          3276700

这里的LENGTH函数显然不是对CLOB进行了隐式的TO_CHAR转换，因为那样的话，要不然因为CLOB长度太大而报错，要不然CLOB自动截取前4000个字符而LENGTH返回的长度则是4000。这里的LENGTH函数明显接收的是CLOB类型的参数。

除了LENGTH外，常用的INSTR、SUBSTR等都是支持LOB字段的：

SQL> DECLARE
  2     V_LOB CLOB;
  3  BEGIN
  4     SELECT CONTENTS
  5     INTO V_LOB
  6     FROM T_LOB
  7     WHERE ID = 1
  8     FOR UPDATE;
  9     DBMS_LOB.WRITEAPPEND(V_LOB, 1000, RPAD('BCDEFG', 1000, 'A'));
 10     COMMIT;
 11  END;
 12  /

PL/SQL procedure successfully completed.

SQL> SELECT DBMS_LOB.INSTR(CONTENTS, 'B')
  2  FROM T_LOB
  3  WHERE ID = 1;

DBMS_LOB.INSTR(CONTENTS,'B')
----------------------------
                     3276701

SQL> SELECT INSTR(CONTENTS, 'B')
  2  FROM T_LOB
  3  WHERE ID = 1;

INSTR(CONTENTS,'B')
-------------------
            3276701

SQL> SELECT DBMS_LOB.SUBSTR(CONTENTS, 10, 3276701)
  2  FROM T_LOB
  3  WHERE ID = 1;

DBMS_LOB.SUBSTR(CONTENTS,10,3276701)
-------------------------------------------------------------------------------------------
BCDEFGAAAA

SQL> SELECT SUBSTR(CONTENTS, 3276701, 10)
  2  FROM T_LOB
  3  WHERE ID = 1;

SUBSTR(CONTENTS,3276701,10)
--------------------------------------------------------------------------------
BCDEFGAAAA

另外，LPAD、RPAD以及LTRIM和RTRIM也是支持CLOB类型的：

SQL> SELECT LTRIM(RTRIM(CONTENTS, 'A'), 'A')
  2  FROM T_LOB
  3  WHERE ID = 1;

LTRIM(RTRIM(CONTENTS,'A'),'A')
--------------------------------------------------------------------------------
BCDEFG

SQL> SELECT LENGTH(LPAD(CONTENTS, 10000000, 'B'))
  2  FROM T_LOB
  3  WHERE ID = 1;

LENGTH(LPAD(CONTENTS,10000000,'B'))
-----------------------------------
                           10000000

字符连接操作||也支持LOB类型：

SQL> SELECT LENGTH(CONTENTS || CONTENTS)
  2  FROM T_LOB
  3  WHERE ID = 1;

LENGTH(CONTENTS||CONTENTS)
--------------------------
                   6555400

如果想找更加详细的支持LOB的函数说明可以参考LOB相关的文档。