1. 首先length和lengthb的区别,如下
length返回的是字符数
lengthb返回的是字节数
如果有中文,那么Length() != Lengthb()
如果没有中文,那么Length() == Lengthb()
http://www.cnblogs.com/kerrycode/p/3749085.html
2. 报错语句如下:
declare
C_content clob:=EMPTY_CLOB();
v_tmpstr varchar2(400);
begin
C_content :=EMPTY_CLOB();
dbms_lob.createtemporary(C_content, TRUE);
dbms_lob.open(C_content, dbms_lob.lob_readwrite);
v_tmpstr:=rpad('姓名',8,' ');
dbms_lob.writeappend(c_content,lengthb(v_tmpstr),v_tmpstr);
dbms_output.put_line(c_content);
end ;
如果截取数据中有中文,那么在9i中能够执行,在11g(11.2.0.1)中测试也是正常,但是在11g(11.2.0.3/11.2.0.4/11.2.0.4.8)中执行会报如下错误:ORA-22921: length of input buffer is smaller than amount requested
3. 官方文档中dbms_lob.writeappend的书写规范:
DBMS_LOB.WRITEAPPEND (
lob_loc IN OUT NOCOPY CLOB CHARACTER SET ANY_CS,
buffer IN VARCHAR2 CHARACTER SET lob_loc%CHARSET);
4. 官方对于参数的释义:
Number of bytes (for BLOBs) or characters (for CLOBs) to write, or number that were written. | |
5. 我的理解
因此,我的理解是,我们语句定义的c_content为CLOB类型,而v_tmpstr为varchar2类型,那么在截取位数amount应获取char位数。而在9i中获取bytes字节也没有报错,应该是9i格式的要求没那么严谨,11g中对该格式要求定义更严格。
应该应该将
dbms_lob.writeappend(c_content,lengthb(v_tmpstr),v_tmpstr);
更改为:
dbms_lob.writeappend(c_content,length(v_tmpstr),v_tmpstr);
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30130773/viewspace-2121129/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/30130773/viewspace-2121129/