Querying CLOB columns

[ronnie]

Hi,
We need a column which can store more than 4000 characters of data approx 20,000 characters need to be stored in that column and we need to be able to query the data based on a where clause or more specifically key words.

I read somewhere that CLOB is better than long datatypes but both have restrictions.

Whats the best way of doing this and how.

I am using Oracle 8.1.7 on a windows 2000 machine.

Thanks
Ronnie

[irehman]

It realy depends what you really want to do with the column,


LONG datatype columns are used in the data dictionary to store the text of view definitions. You can use LONG columns in SELECT lists, SET clauses of UPDATE statements, and VALUES clauses of INSERT statements

CLOBs participate fully in transactions. Changes made to a CLOB value by the DBMS_LOB package, PL/SQL, or the OCI can be committed or rolled back. However, CLOB locators cannot span transactions or sessions

So clearly you need to know what you are trying to accomplish here.

[ronnie]

Hi,
Thanks for the reply.

All I need to be able to do here is to search for a particular key word and display all the records in the clob column which match the criteria and also it would be good if i am able to commit or rollback the transaction.

Please Suggest

Ronnie

[ronnie]

Still Waiting for some help

Ronnie

[Shestakov]

I mean, you can use only stored function for search operation in CLOB fields.
In this function you can execute any funttions/procidure from DBMS_LOB package.

May be you may create FUNCTION-BASED INDEX for better perfomance of search operation on CLOB fields.