Re: Varchar2

From: William Robertson <>
Date: Sun, 2 Mar 2008 01:04:17 -0800 (PST)
Message-ID: <>

On Mar 1, 12:30 am, "" <> wrote:
> Hi,
> I'm looking around for opinions.
> We're running Oracle 10g, we have the need now for a VARCHAR2 column
> with 6000 characters. I know the standard thing is to create a LOB
> column.
> We have not LOB column in our database. I have 2 options here:
> The first is to create 2 columns, and, use a function on SELECT and a
> trigger in INSERT to split or concatenate the string.
> The second is to use a LOB column, and if I do that, it may have to be
> compatible with PHP, as PHP may be selecting the data......maybe.
> So, I'm looking for opinions on people who have done this, or used LOB
> columns, since we never have....
> Thanks!

I don't see how an all-VARCHAR2 approach would be possible anyway since concatenating VARCHAR2s produces a VARCHAR2 which of course is still limited to 4000 bytes, and anything over that will give "ORA-01489: result of string concatenation is too long". The only way I can see to avoid that would be something like

SELECT TO_CLOB(c1) || TO_CLOB(c2) FROM yourtable;

and if the idea was to avoid CLOBs you're back where you started, not to mention the pain of any attempt to index your fake CLOB or search it for the word 'banana'. Received on Sun Mar 02 2008 - 03:04:17 CST

Original text of this message