编程中发现存储过程、函数返回的long类型过大时，报ORA-06502: PL/SQL: 数字或值错误 : 字符串缓冲区太小
PL/SQL中long变量只允许存储32768字节（32K）
超过建议使用clob
Hi,
I've a procedure written in PL/SQL where in application numbers are getting concatenated to a variable in loop. And the length is exceeding 32767 ( Max for VARCHAR2) . Then I tried with LONG datatype for that variable but same happenes there. Then I made the variable as CLOB. IT works and even the functions like SUBSTR,LENGTH also works on variable of type CLOB. Surprised to see this
Can anybody explain me the reason :-
1 Why LONG doesn't accept value more than 32760 in Pl/SQL .
2. Why substr and length functions worked with CLOB datatype when they were supposed to use DBMS_LOB package
3.Is there any performance impact of using variable as CLOB in Procedure for storing longer strings
Thanks
Sunil
Hi, the same kind of problem i am having....
for that i tried clob as pl/sql variable and it gets >32k and shows length 43K. and i inserted this into long column and i didn't say any error. i selected the long database column into clob pl/sql variable and it gives only 4000 bytes of data.
and everybody replies me to change the data type to clob in table.But I can't change it into clob datatype since i have to do some modifications in existing appl. and the table is referred by many procedures and functions.
Thanks,
Dhamayanthi K.
在oracle中，有4个大对象（lobs）类型可用，分别是blob,clob,bfile,nclob。
下面是对lob数据类型的简单介绍。
blob:二进制lob，为二进制数据，最长可达4GB，存贮在数据库中。
clob:字符lob,字符数据,最长可以达到4GB,存贮在数据库中。
bfile:二进制文件;存贮在数据库之外的只读型二进制数据，最大长度由操作系统限制。
nclob：支持对字节字符集合(nultibyte characterset)的一个clob列。
对于如何检索和操作这些lob数据一直是oracle数据库开发者经常碰到的问题。下面我将在oracle对lob数据处理的一些方法和技巧，介绍给读者，希望能够对读者以后的开发有所帮助。
oracle中可以用多种方法来检索或操作lob数据。通常的处理方法是通过dbms_lob包。
在oracle开发环境中我们可以用dbms_lob包来处理！dbms_lob包功能强大，简单应用。既可以用来读取内部的lob对象，也可以用来处理bfile对象。但处理两者之间，还有一点差别。处理内部lob对象（blob,clob）时，可以进行读和写，但处理外部lob对象bfile时，只能进行读操作，写的操作可以用pl/sql处理。另外用sql也可以处理lob，但要注意sql仅可以处理整个lob，不能操作lob的数据片。
在dbms_lob包中内建了read(),append,write()，erase()，copy(),getlength()，substr()等函数，可以很方便地操作lob对象。