关键字:
CLOB、BLOB、DBMS_LOB.SUBSTR、UTL_RAW.CAST_TO_VARCHAR2、
ORA-06502: PL/SQL: 数字或值错误 : raw variable length too long
ORA-06512: 在 line 1
、
ORA-01704
数据流处理过程中,由于DBMS_LOB.SUBSTRB,无法进行字节长度截取导致,经常遇到DBMS_LOB.SUBSTR截取长度依旧无法入库情况。通过自定义函数,精准截取指定长度。
clob_substrb
create or replace function clob_substrb(colb_str in clob,a in number,b in number) return varchar2 is
str varchar2(32767);
begin
str := DBMS_LOB.SUBSTR(colb_str, 32767, 1);
str := substrb(str, a, b);
return str;
exception
when others then
return NULL;
end;
blob_substrb
create or replace function blob_substrb(bolb_str in blob,a in number,b in number) return varchar2 is
str varchar2(32767);
begin
str := UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(bolb_str, 32767, 1));
str := substrb(str, a, b);
return str;
exception
when others then
return NULL;
end;