Newbie Question: CLOBs, LONG and text fields

Hey Folks,

Please forgive the complete ignorance, but I have a question regarding text datatypes and table creation. Further, I am coming from a Linux (Postgres/MySQL) and Microsoft (SQL Server/Access) environment and have not run into this type of developmental/structural issue yet, so here goes:

I am attempting to create a knowledge base. One of the columns in the main "notes" table has the purpose of storing textual data (such as a kb article). As I understand it, I could use LONG, but can't use LONG's in a where clause. I could use a CLOB (and every bit of literature I read indicates I should be doing this), but if the text is larger than 4k, this doesn't get stored inline. Further, I am having a problem understanding how to check for given data within a CLOB.

1. Given table:

NOTES:

ID                  NOT NULL NUMBER(5)
TITLE            VARCHAR2(128)

TYPEID         NUMBER(3)
BODY           CLOB

2. And the following statement:

SELECT * FROM NOTES WHERE TITLE LIKE '%ABC'% or BODY LIKE '%ABC%';

3. I get "ORA-00932: inconsistent datatypes" error.

Presumably, I get the error due to the fact that the column actually stores the location to the CLOB and not the CLOB itself. My question(s) is: Is there an easy'ish way to get the above method to work? Is there an alternative that works equally well? Is my syntax off? Should I be using some other datatype I haven't come across yet? Or am I completely loony and this simply can't be done??!?!?

Note: I have read that interMedia can help solve this problem, but am hesitant to go that route, as I simply don't know enough about it.

Comments? Questions? Solutions?

Any and all help is appreciated.

Jason Received on Fri Nov 07 2003 - 02:34:00 CET