Will CLOB data be truncated when displayed in a SELECT query? If so, how can it be displayed?

Question

Hi folks,

I've got a Varchar2 field in my table which I want to convert to a CLOB. I am unsure whether the data would get truncated when selected. If so, what is the limit and does it depend on the database settings?

In my TOAD or SQLPLUS window it gets truncated but this may just be the environment settings. I'm not sure whether it would get truncated in my actual application (I can test this, but up to what size should I test?)

If it does get truncated, what's the best way to display the whole CLOB? There are other fields in my SELECT query, so I think I can't just loop through multiple rows. Is there any way out?

Thanks for your help.

Answer 1

In 10gR2 a CLOB can hold "(4 gigabytes - 1) * (database block size)", and the database block size is typically 8k.

So a CLOB can, in theory, be a terabyte of data which would be beyond the scope of most machines to cope with.

So the answer to "what size should I test" is whatever size you decide. I'd go with an order of magnitude larger than the limit I expect to process so if I'd was expecting a 5 MB value, I'd test with 50 MB. 50MB would be very big for text (though it may be appropriate if it was a log file of some sort).

Answer 2

If you select a clob value from a table it depends on your application how it will handle it. If you change a varchar2 column to a clob column you will need to change your application so that it retrieves the clob data correctly from the database. So I guess the answer is: It is possible to get it working correctly.