Re[2]: CLOB

Saturday, November 04, 2000, 2:10:21 AM, Alexander wrote:

>> All LOBs can store only 4000 chars in line. If you
>> have to store more than that then you have to store
>> it outside the table in separate tablespace.
>>

AIO> That are you talking about ?!!!! 
AIO> All LOB's can store up to 4 GigaBytes !
AIO> Satya can't you explain in details hw you writing into CLOB
AIO> ?
AIO> may be the problem iz what you can't write more than 4000 byte in one time ..
 

LOBs (BLOB, CLOB, BFILE) can indeed store up to 4GB. However, there is a limit to how much of that 4GB is stored "inline" with other data from the same row. The limit may be 4KB, I don't remember. Whatever the limit, LOB data in excess of that limit must be stored "out of line", which means separately from the rest of the row. You would probably be best off using a separate tablespace for out of line storage. The reason for all this is to keep table scans reasonably efficient. You don't want to read a bunch of 4GB LOB columns each time you do a full table scan.

As for getting more than 4KB bytes into a LOB at one time, the original poster may be encountering the limit on VARCHAR2 length. It sounds like he was trying to pass a string literal. My only LOB experience is using the DBMS_LOB package. Using DBMS_LOB, you can write data into a LOB one piece at a time. That gets you around the VARCHAR2 size limit. There may be other solutions. DBMS_LOB is just the one that I'm familiar with.

-- 
Best regards,
 Jonathan                            mailto:jonathan_at_gennick.com