DBMS_LOB.SUBSTR(col1,n,pos) : 获取文本

DBMS_LOB.SUBSTR(col1,n,pos) : 获取文本

eg:
DBMS_LOB.SUBSTR(col1,10,1) 表示从第1个字节开始取出10个字节.
DBMS_LOB.SUBSTR(CLOB_VAR,32767) 表示截取CLOB变量保存的全部数据。

代码示例：
建表

create table bak_DBMS_LOB_0302(
  bak_id number(4),
  bak_comment clob
);
commit;

插入测试数据

delete from bak_dbms_lob_0302;
insert into bak_dbms_lob_0302(bak_id,bak_comment) values(1,'a');
insert into bak_dbms_lob_0302(bak_id,bak_comment) values(2,'ab');
insert into bak_dbms_lob_0302(bak_id,bak_comment) values(3,'abcdefgccccccc');
insert into bak_dbms_lob_0302(bak_id,bak_comment) values(4,'a   bcdefg');
commit;

1.获取字段长度

select dbms_lob.getlength(bak_comment) from bak_dbms_lob_0302;

2.获取字段全部数据

select dbms_lob.substr(bak_comment,32767) from bak_dbms_lob_0302;

3.从第1个字节开始取出5个字节，查询出的bak_comment字段乱码

select b.bak_id, utl_raw.cast_to_varchar2(dbms_lob.substr(bak_comment,5,1))  from bak_dbms_lob_0302 b;

4.从第1个字节开始取出5个字节，正常显示，即使clob中包含空格仍能正常显示

select b.bak_id, dbms_lob.substr(bak_comment,5,1) from bak_dbms_lob_0302 b for update;

注：通过dbms_lob.substr（）转换clob字段为varchar2类型