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

BD wrote:
> I was approached by a developer this morning, who has a SQL server
> database that needs to be transferred to Oracle.
>
> The SQL database has a few columns that are varchar columns, and are
> longer than 4000 bytes.
>
> At this point I know of no way to migrate this data across to Oracle
> directly, because of the 4000 byte limit.
>
> I've done some reading in the archives on *why* the limit exists, and I
> acknowledge the chained rows and I/O issues.
>
> I also stumbled across an article in which someone 'got around' the
> limitation by splitting the field into several fields, and using a view
> to concatenate the data, as in 'select text_field1 || text_field2 from
> split_field'...
>
> I presume that DBAs have encountered the need to supply more than 4000
> characters in a single field in the past, and am curious as to what
> strategies might be used to accomplish this.
>
> Comments welcome.

If you cannot use a BLOB or CLOB the only good alternative you have is to change the column you are migrating from back to 4000 characters. Then your problem goes away.

Perhaps you could get really ugly with multiple columns and joining them in a view then using a trigger to split out the data when it gets inserted/updated. But that's not where I would want to go. Received on Fri Nov 10 2006 - 12:41:22 CST