Re: Any chance to surpass 4000 character limit?

From: Vladimir M. Zakharychev <>
Date: Mon, 26 Jan 2009 07:34:04 -0800 (PST)
Message-ID: <>

On Jan 26, 5:27 pm, wrote:
> On 26 Jan., 13:11, "Vladimir M. Zakharychev"
> <> wrote:
> ...
> > CLOB and an Oracle Text index over it should do the trick. You will
> > need to change the way you *search* that column though (use CONTAINS()
> > operator instead of LIKE/REGEXP_LIKE,) so this might not be an option
> > for you.
> Hi Vladimir,
> thanks for that idea, I have to check this. Our applications are
> connecting to several DBMS depending on customer preferences, besides
> Oracle we also work with MS SQL Server and MySQL. Both of those
> support longer character columns. We are using ODBC to connect to the
> database and of course we prefer to use the same generated SQL
> statements when dealing with any DBMS, but on the other hand sometimes
> we have to deal with DBMS differences. This might be such a case.
> With such an indexed CLOB, would it be possible to search for any
> string within the column or just for whole words? I. e. could I use
> the Contains() clause just in the same way as a LIKE '%..%' clause? We
> need to search any sort of character sequence within such a text, for
> example part of a name.
> > By the way, the limit is 4000 BYTES, not CHARACTERS (when working with
> > single-byte charsets you might never feel the difference, but as soon
> > as you go international you will almost immediately bump into it.)
> Thanks for pointing this out, but fortunately this will never be an
> issue for us, because our apps are created exclusively for our
> national market.
> Cheers
> Kay-Viktor

Well, Oracle Text gives a lot more than LIKE, but you can, of course, use it the same way as you use LIKE. Consider these examples:

SELECT id FROM mytable WHERE CONTAINS(mytextcolumn, 'word') > 0 looks for "word", exactly

SELECT id FROM mytable WHERE CONTAINS(mytextcolumn, '%{or}%') > 0 looks for "or" substring (since "or" itself is a keyword in Text queries, we need to enclose it with curly braces here.)

SELECT id FROM mytable WHERE CONTAINS(mytextcolumn, 'about(word)') > 0 Theme search: looks for texts about "word", but not necessarily having the word itself.

SELECT id FROM mytable WHERE CONTAINS(mytextcolumn, '$word') > 0 Stemmed search: looks for "word" and other terms with the same linguistic root (like "words.")

SELECT id FROM mytable WHERE CONTAINS(mytextcolumn, '?word') > 0 Fuzzy search: looks for "word" and all words spelled similarly.

Oracle Text Reference is a must-read if you want to play with it.


   Vladimir M. Zakharychev
   N-Networks, makers of Dynamic PSP(tm) Received on Mon Jan 26 2009 - 09:34:04 CST

Original text of this message