SELECT UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(col1,10,1)) FROM tab1;试试看
给你一个存储过程,你试试调用 create or replace function getclob( table_name in varchar2, field_id in varchar2, field_name in varchar2, v_id in number, v_pos in number) return varchar2 is lobloc clob; buffer varchar2(32767); amount number := 2000; offset number := 1; query_str varchar2(1000); begin query_str :='select '||field_name||' from '||table_name||' where '||field_id||'= :id '; EXECUTE IMMEDIATE query_str INTO lobloc USING v_id; offset:=offset+(v_pos-1)*2000; dbms_lob.read(lobloc,amount,offset,buffer); return buffer; exception when no_data_found then return buffer; end;
获取文本 DBMS_LOB.SUBSTR(col1,n,pos)
如DBMS_LOB.SUBSTR(col1,10,1)表示从第1个字节开始取出10个字节
create or replace function getclob(
table_name in varchar2,
field_id in varchar2,
field_name in varchar2,
v_id in number,
v_pos in number) return varchar2
is
lobloc clob;
buffer varchar2(32767);
amount number := 2000;
offset number := 1;
query_str varchar2(1000);
begin
query_str :='select '||field_name||' from '||table_name||' where '||field_id||'= :id ';
EXECUTE IMMEDIATE query_str INTO lobloc USING v_id;
offset:=offset+(v_pos-1)*2000;
dbms_lob.read(lobloc,amount,offset,buffer);
return buffer;
exception
when no_data_found then
return buffer;
end;