If you store a large text data as a CLOB
data type in your Oracle database, you may come across to the error messages below when you query your table:
ORA-06502: numeric or value error: character string buffer too small
ORA-01489: result of string concatenation is too long
Background
CLOB
data type is used to store large text data (up to 2 GB in MySQL and up to 128 TB in Oracle). Similarly, BLOB
data type is used to store large binary data (videos, images, documents).
If you want to retrieve data from a CLOB
field, you should convert data to a string data type. VARCHAR2
is commonly used string data type. However, it has a limitation of 4000 characters.
In my case, I use the following query to retrieve the data. Since I am requesting 5000 characters and VARCHAR2
only supports 4000, I receive the errors above.
select dbms_lob.substr(column1,5000,1) from DIM_SW_TEST;
Solution
Write the same column name twice in the same SELECT
query. So basically you divide the content in two VARCHAR2
fields (total of up to 8000 characters). Make sure to increase the buffer size with the SET BUFFER
parameter.
set buffer 10000; select dbms_lob.substr(column1,4000,1), dbms_lob.substr(column1,4000,4001) from DIM_SW_TEST;