Re: Which datatype for Text Field 32k MAX

John,

I don't think you could use the Oracle CHAR (fixed length string) or VARCHAR (variable length string) as these would be limited to 2000 and 4000 characters respectively. This would restrict you to using the LONG datatype or the newer CLOB datatype.

One of my clients has implemented a searchable, historic news database using the LONG datatype. The searching of the database is done using the Oracle context server (also known as interMedia in newer releases of Oracle). This consumes a lot of resources but it indexes all significant words in around half a million short financial news articles. You can do a keyword search with a query like SELECT ARTICLE_ID FROM ARTICLE_TABLE WHERE CONTAINS (ARTICLE_TEXT,'keyword') > 0 ; I believe CNN also use Oracle interMedia with their searchable news archive.

If you intend to use interMedia, it may be worth upgrading to Oracle 8.1.6. My client initially used 8.1.5 on NT but there was a bug in it that cased a deadlock to arise when the interMedia server process was running. On 8.1.5/NT, the interMedia server would not stay up for more than about 20 minutes! I'm not sure whether it only afflicts Oracle running under NT but Oracle support assure us that this is fixed in 8.1.6.

I believe that there is some doubt about the future of the LONG data type and whether it is guaranteed to be supported in future versions of Oracle. I think Oracle's intention is to replace LONG with LOB. If you are developing a new database, it might be worth considering the use of LOB datatypes because of this. I think the CLOB datatype can be used with interMedia.

Having said this, you would need to make certain that the application you are using to access the data (ODBC?) can access CLOB fields. I know that my client had problems accessing CLOB fields using Microsoft products.

Another client experienced problems accessing NCLOB data on an 8.1.6 database running under Solaris. Again this was due to a bug (and there is a patch for it) but I think it only happened when the database was being accessed remotely using SQL*Net and only for the NCLOB datatype. This was being used to hold Japanese text.

In summary, I would say that CLOB is probably the best option if your front end applications can extract CLOB data and if you can avoid the bugs detailed above, otherwise use LONG. I don't think you can search LONGs and CLOBs using ordinary SQL operators (e.g WHERE ARTICLE_TEXT LIKE '%keyword%') so it might be worth considering interMedia and Oracle 8.1.6. If your records are date stamped and if you have a lot of them, it might also be worth considering the use of partitioning to speed up queries.

I hope the above is of some use to you. Please let me know if you need any more information.

Regards,

Richard

JF <john.fitzgerald_at_mainframe.co.uk> wrote in message news:CU056.8919$nu5.85805_at_NewsReader...
> Folks,
>
> I'm new to Oracle (8.1.5) and have the following question with regards
> building a database design
>
> I have a field that will contain Ascii text (Article)
> The max number of chars is 32k
> The field must be searchable (where article like etc.)
>
> The calling application will be ASP(VB) / PHP4 based web pages
>
> Previously I've used a MEMO field in MS Access and a TEXT field in SQL
> Server to acheive the above, however there does not seem to be a direct
> equivalent in Oracle please HELP!!!!!!!!!
>
> I can't use LONG cause you can search a LONG field, I've been messing
 about
> with CLOB but it seems just to hold somesort of reference which means I
> can't even select a record that has a CLOB field using just SQL nor for
 that
> matter use it in a where clause.
>
> Any suggestions would be greatly appriecated !
>
> Cheers
> John
>
>
>
Received on Thu Jan 04 2001 - 18:55:21 CST