Re: CLOB Length as 4000

DA Morgan wrote:
> Anurag Varma wrote:
> > 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 cannot define a CLOB length. It can store upto 4G of data.
> > http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements001.htm#sthref174
> >
> > Anurag
>
> All true but that is not what is stored in the referenced column
> in the data dictionary. Thus, I think, the OP's question.
> --
> 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

I might be wrong ... but I believe the referenced column shows it correctly. The inline length max is 4000. If the length of clob exceeds that then its stored out of line with a pointer to it.

Anurag Received on Wed Jun 21 2006 - 16:50:32 CDT