Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> 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.
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 Thu Nov 06 2003 - 21:24:51 CST