Querying CLOB columns
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Querying CLOB columns

  1. #1
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577
    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

  2. #2
    Join Date
    Dec 2000
    Location
    Virginia, USA
    Posts
    455
    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.

  3. #3
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577
    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

  4. #4
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577
    Still Waiting for some help

    Ronnie

  5. #5
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width