Re: CLOB Length as 4000

Sundar wrote:
> Hi,
>
> I have a table in our source system which has defined the CLOB length
> as 4000. This length is available in the field DATA_LENGTH in
> ALL_TAB_COLUMNS. But when I search the table to get the max length for
> the CLOB field using the function DBMS_LOB.GETLENGTH(col1), I got a
> number around 23000.
>
> So it look lilkes the length definition does not matter for CLOB and it
> doesnt look like restricting the users from entering data more than
> 4000.
>
> The reason I am looking at the length is I am planning to convert the
> CLOB field to VARCHAR without losing anydata from the source.
>
> Can anyone confirm what it means when the table has CLOB (4000)
>
> Would really appreciate your help!!
>
> Thanks in advance,
> Sundar.
>

You can look these things up in $ORACLE_HOME/rdbms/admin/catalog.sql

You could also find it with the following: SELECT column_name, comments
FROM user_col_comments
WHERE table_name =

The to answer your immediate question:
comment on column USER_TAB_COLS.CHAR_COL_DECL_LENGTH is 'Declaration length of character type column'

The only way to truly tell the size of what is in the column is:

SELECT MAX(dbms_lob.length(<clob_column_name>)) FROM <table_name>;

And it is what it is.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org