The Oracle Text Reference 10g states that when we index our table using multi-column datastore, the system concatenates the text columns, tagging the column text, and indexes the text as a virtual XML document for each row.

Also, in certain situations, we can use user datastore to define stored procedures that synthesize documents during indexing. The procedure can grab the data and put them in an XML format. A good example is shown here.

The Question

Is there a way to retrieve or display the underlying virtual XML document for each record? How?

  • Note: this question was originally posted at serverfault.com/q/214375/59289, but seems like nobody could help me over there.
    – Gan
    Commented Jan 7, 2011 at 9:42
  • Can you rephrase the title as a question? Commented Jan 7, 2011 at 9:49
  • @Brian, done :)
    – Gan
    Commented Jan 7, 2011 at 10:13
  • On an interesting, but unrelated note, there's an automated way to make a concordance for a document, the SNIPPET function. That's all kinds of awesome. Commented Jan 7, 2011 at 10:29
  • @Brian, the SNIPPET function is for Oracle 11g, is it? I'm working on [oracle-10g] here, but thanks for the info by the way. :)
    – Gan
    Commented Jan 7, 2011 at 10:43

1 Answer 1


Probably not

The view that may display native stored compiled indexes is CTX_INDEX_VALUES Or CTX_USER_INDEX_VALUES (depending on permissions and querying user). However, given that the index value length is only 500 characters, it is unlikely that it is stored there. (Just start selecting randomly from those indexes, if it exists, it will likely be there.)

Looking at the view design, however, it is unlikely that those virtual XML elements are stored, as it's much easier to compile them on the fly and then load them into memory.

Alternatively, CTX_DOC has a number of markup functions, but none indicate the ability to access native XML.

You may also want to explore the discussion around metalink: 122255.1 (Unfortunately, I don't currently have metalink access).

It may be easier to write your own parser-to-XML (as oracle has quite a few excellent native XML functions) than try to hack around the full text index.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.