Long variables in PL/SQL can only store 32768 bytes

In the oracle development environment, we can use the dbms_lob package for processing! Dbms_lob package is powerful and simple to use. It can be used to read internal lob objects or to process B

In the oracle development environment, we can use the dbms_lob package for processing! Dbms_lob package is powerful and simple to use. It can be used to read internal lob objects or to process B

The ORA-06502: PL/SQL: Number or value error: string buffer is too small when a stored procedure is found and the long type returned by the function is too large

Long variables in PL/SQL can only store 32768 bytes (32 K)
Clob is recommended

Hi,
I 've 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 2 gb of data. as if having 2 gb of size, there shoshould be some way to insert that much data. so I want to know what is the way to store more than 32 k by a pl/SQL variable?

For that I tried clob as pl/SQL variable and it gets> 32 k and shows length 43 K. 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 using procedures and functions.

Thanks,
Dhamayanthi K.

In oracle, four lobs types are available: blob, clob, bfile, and nclob.

The following is a brief introduction to the lob data type.

Blob: Binary lob, Which is binary data. It can be up to 4 GB and stored in the database.

Clob: character lob, character data, up to 4 GB, stored in the database.

Bfile: binary file; read-only binary data stored outside the database. The maximum length is limited by the operating system.

Nclob: supports a clob column of the byte character set (nultibyte characterset.

Oracle Database developers often encounter lob data retrieval and operation. I will introduce some methods and skills for lob data processing in oracle, and hope to help readers in future development.

Oracle can use multiple methods to retrieve or operate lob data. The common solution is to use the dbms_lob package.

In the oracle development environment, we can use the dbms_lob package for processing! Dbms_lob package is powerful and simple to use. It can be used to read internal lob objects or process bfile objects. However, there is a difference between the two. Read and Write operations can be performed when processing internal lob objects (blob, clob), but read operations can only be performed when processing external lob object bfile, write operations can be processed using pl/SQL. In addition, you can use SQL to process lob, but note that SQL can only process the entire lob and cannot operate on lob data slices.

The dbms_lob package has built-in functions such as read (), append, write (), erase (), copy (), getlength (), and substr, you can easily operate lob objects.