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)
MODIFIED NOT NULL DATE
SECLEVEL NUMBER(3)
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
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)
MODIFIED NOT NULL DATE
SECLEVEL NUMBER(3)
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