Re: [PL/SQL NEWBIE]Detect Varchar2 field length

From: Mark Bole <makbo_at_pacbell.net>
Date: Tue, 30 Nov 2004 00:31:18 GMT
Message-ID: <qbPqd.51730$QJ3.4251@newssvr21.news.prodigy.com>

DA Morgan wrote:

> Alex NSB wrote:
> 

>> Catherine wrote:
>>
>>> Perhaps a function that will check
>>> LENGTH(whatever) <= DATA_LENGTH
>>> where DATA_LENGTH is queried from ALL_TAB_COLUMNS?
>>> Good luck!
>>
>>
>>
>> Catherine,
>>
>> thanks for taking time to reply.
>>
>> I always tend to forget the existence of those tables, so your tip was
>> really helpful.
>>
>> I only had one minor issue with CLOBs (the reported length is 4000) but I
>> worked around it.
>>
>> Regards.
>>
>> Alessandro

> 
> 
> CLOBS are 4GB not 4KB.

But the first 4K can be stored in-line in the table, that is the 4K that is being seen, otherwise you have to go look at the LOB segments to get the size. (join DBA_LOBS with DBA_SEGMENTS, for example).

http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96591/adl06faq.htm#123949

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_73a.htm#2128959

-Mark Bole Received on Mon Nov 29 2004 - 18:31:18 CST