ORACLE Text operations in spanned varchar2's

Posted on 2011-10-20
Last Modified: 2012-05-12
Greetings EE warriors,
I require to do fuzzy logic on spanned text.  
if you look at the table below, I have an article that spans parts by artid (article id), and partid.
article one has three parts.  The article is split at 4000 characters, due to the limitations of varchar2.

artid          partid              article
1              1                   The quick brown dog jumped over the lazy yellow dog
1              2                   The green dog watched the brown dog and then saw a tr
1              3                   ee and bit it.  The blue dog just went to sleep.
2              1                   Three cats went up into the tree to play poker.  The fir
2              2                   st cat went to bid on two aces, while the second cat went
2              3                   and folded.  The third cat won the hand.

I need to do a "NEAR" operation on the article.  The operation must pretend that the article is in whole, and not in parts, for example, tree is split between part 2 and 3.  If I do a word search on tree, it must find it.  Secondly, if word1 is in part 1, and word 2 is in either part 1 or part 2, it must come back true.

Question by:Evan Cutler
    LVL 76

    Expert Comment

    by:slightwv (䄆 Netminder)
    From your previous question I thought you were going to go with a CLOB?

    Easiest way is a user datastore. There is even an example of this exact requirement in the docs: USER_DATASTORE with CLOB Example
    LVL 73

    Expert Comment

    assuming you don't have too many parts per article, try constructing a clob out of the strings...

    select artid,EXTRACT(XMLAGG(XMLELEMENT("x", article|| ' ') order by partid), '/x/text()').getclobval() from yourtable
    group by artid

    then apply the TEXT operations to that
    LVL 76

    Expert Comment

    by:slightwv (䄆 Netminder)
    Unfortunately Text indexes need to be against a physical object.  The other option is a Materlialzed View.

    If you create the user datastore you can use that method as well to create the CLOB inside the procedure.  However, I suspect the dbms_lob.writeappend will outperform XMLAGG.

    LVL 9

    Author Comment

    by:Evan Cutler
    Slightwv: Clob is answer 1.
    I'm asked to develop a few courses of action.
    I am not sure the business layer can accept I have to look into multiple avenues.
    LVL 76

    Accepted Solution

    Cool.  Just checking.

    The User Data Store is the way to go.

    Use either option to get the CLOB in the stored procedure.

    There might even be more options to build a CLOB.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
    Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
    This video shows how to recover a database from a user managed backup
    This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now