Re: The need to 'get around' the 4000 character limit in varchar2

From: hpuxrac <johnbhurley_at_sbcglobal.net>
Date: 12 Nov 2006 07:48:34 -0800
Message-ID: <1163346514.888727.247820@k70g2000cwa.googlegroups.com>

Thomas Kellerer wrote:
> hpuxrac wrote on 12.11.2006 00:57:
> > Thomas Kellerer wrote:
> >> hpuxrac wrote on 11.11.2006 01:47:
> >>>>> Tx!
> >>>> Move it to a CLOB.
> >>> You are missing the other previous relevant answers and exchanges in
> >>> this thead.
> >> > Wonder how many people already suggested that one.
> >>
> >> Because moving to CLOB is the only sensible solution.
> >
> > I wonder what my first suggestion was in this thread.
> >
> > Why don't you read it from the beginning?

>

> I did read it. But your solution is still a workaround (and an ugly one as well
> in my opinion). I don't think using multiple varchar2 columns require less code
> changes in the application that switching from varchar2 to CLOB (as the OP has
> indicated).

>

> Switching back to the limit of 4000 does not even seem to be a solution to me,
> as the requirement is obviously to store more characters than that. But if the
> reality shows, that no more than 4000 are indeed used (even in SQL Server) then,
> yes I agree with you

Thomas I originally noted that "if the OP cannot use BLOB or CLOB" THEN ... blah blah blah.

Sorry if this confused you but perhaps I should have stated it more explicitly. Obviously using a BLOB/CLOB appears to be the best or at least most clean solution.

That's going to require application changes and testing etc, just as any application originally written against SQL server and migrated to oracle will require ( at least testing ) and perhaps/probably some amount of application changes.

Potentially putting on a bandaid with multiple varchar2 columns would be ugly and a scalability concern but perhaps using this approach with a view and a public synonym might be be more transparent, not that this is necessarily a good thing.

Tom Kyte has this whole area ( designing an application to take advantage of the features provided by a database vendor versus database independence ) covered quite well in several of his books as well as his ask tom site. Received on Sun Nov 12 2006 - 09:48:34 CST