Re: Newbie Question: CLOBs, LONG and text fields

On Fri, 07 Nov 2003 03:24:51 GMT, "M.E.R.C" <m.e.r.c_at_rogers.com> wrote:

>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
>
>

Just read up on the dbms_lob package, which contains functions like dbms_lob.instr and dbms_lob.substr

--
Sybrand Bakker, Senior Oracle DBA