[Last Call] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 244
  • Last Modified:

ORACLE Text operations in spanned varchar2's

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.

Evan Cutler
Evan Cutler
  • 3
1 Solution
slightwv (䄆 Netminder) Commented:
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

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
slightwv (䄆 Netminder) Commented:
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.

Evan CutlerAuthor Commented:
Slightwv: Clob is answer 1.
I'm asked to develop a few courses of action.
I am not sure the business layer can accept clobs....so I have to look into multiple avenues.
slightwv (䄆 Netminder) Commented:
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.

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now