Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
Note: this question was originally posted at, but seems like nobody could help me over there. – Gan Jan 7 '11 at 9:42
Can you rephrase the title as a question? – Brian Ballsun-Stanton Jan 7 '11 at 9:49
@Brian, done :) – Gan Jan 7 '11 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. – Brian Ballsun-Stanton Jan 7 '11 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 Jan 7 '11 at 10:43
up vote 5 down vote accepted

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.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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