PL/SQL中long变量只允许存储32768字节

PL/SQL中long变量只允许存储32768字节

编程中发现存储过程、函数返回的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....

i have seen that long pl/sql variable can have only 32760. but same time in Oracle database, long (as database column) can have upto 2gb of data. as if having 2gb of size, there should be some way to insert that much data. so i want to know what is the way to store more than 32k by a pl/sql variable?

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对象。